﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.DAL
{
    public sealed class D_FIM_Payments : IDAL_FIM_Payments
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();


        public D_FIM_Payments()
        {

        }

        public IList<M_FIM_Payments> GetFull(Nullable<int> bgm_source_seq
            , Nullable<int> bgm_year, Nullable<int> work_area_seq
            , Nullable<int> event_seq, string direct_pay)
        {
            string strSQL;
            string strField = "";
            string strJoin = "";
            DataTable dtb = null;
            IList<M_FIM_Payments> infoList = new List<M_FIM_Payments>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_year", bgm_year);
                dbManager.AddParameters(3, "@work_area_seq", work_area_seq);
                dbManager.AddParameters(4, "@event_seq", event_seq);
                dbManager.AddParameters(5, "@direct_pay", direct_pay);

                switch (event_seq)
                {
                    case 1: // ส่งรายได้แผ่นดิน
                        strField = strField + "";
                        strJoin = strJoin + "";
                        break;
                }

                strSQL = "SELECT pay.pay_seq, pay.bgm_source_seq, pay.work_area_seq, pay.bgm_year"
                    + ", pay.event_seq, pay.pay_date, pay.real_pay_date, pay.ref_no, fim_money_requests.ref_no ref_no_money_request"
                    + ", pay.receipt_no, pay.receipt_name, pay.pay_name, pay.method_seq"
                    + ", pay.money_req_seq, pay.petition_seq, pay.petition_dtls_seq"
                    + ", pay.emp_seq, pay.contact_seq, pay.bidder_seq, pay.po_seq"
                    + ", pay.amount, pay.vat_rate, pay.vat, pay.tax_rate, pay.tax"
                    + ", pay.post_pay_seq, pay.tax_pay_seq"
                    + ", pay.penalty_amount, pay.redemption_amount"
                    + ", pay.tax_invoice_id, pay.tax_invoice_date"

                    + ", s.bgm_source_name, area.work_area_name, event.event_name"
                    + ", concat(np.prefix_short, emp.first_name, ' ', emp.last_name) as emp_text"
                    + ", c.NameTh as contact_name, m.method_name"
                    + ", case when pay.contact_seq is not null then c.NameTh"
                            + " else concat(np.prefix_short, emp.first_name, ' ', emp.last_name)"
                        + " end as payable_name"

                    + ", pay.approved_by, pay.approved_when, pay.record_status"

                    + ", case when pay.method_seq = 2"
                        + " then case when coalesce(pc3.pay_cheque_count, 0) > 0 then 'เช็คขึ้นเงินแล้ว'"
                            + " when coalesce(pc2.pay_cheque_count, 0) > 0 then 'จ่ายเช็คแล้ว'"
                            + " else rs.record_status_name"
                            + " end"
                        + " else case when pay.real_pay_date is not null then 'จ่ายแล้ว'"
                            + " else rs.record_status_name"
                            + " end"
                        + " end as record_status_name"
                    + ", pay.created_by, pay.created_when, pay.updated_by, pay.updated_when, pay.from_bank_acc_id, pay.to_bank_acc_id"
                    + " FROM fim_payments pay"
                    + " LEFT JOIN fim_money_requests ON pay.money_req_seq = fim_money_requests.money_req_seq"
                    + " LEFT JOIN bgm_mas_source s ON pay.bgm_source_seq = s.bgm_source_seq"
                    + " LEFT JOIN fim_mas_methods m ON pay.method_seq = m.method_seq"
                    + " LEFT JOIN fim_mas_events event ON pay.event_seq = event.event_seq"
                    + " LEFT JOIN IcmMasContact c ON pay.contact_seq = c.ConId"
                    + " LEFT JOIN fim_petition_dtls pd ON pay.petition_dtls_seq = pd.petition_dtls_seq"
                    + " LEFT JOIN fim_petitions p ON pd.petition_seq = p.petition_seq"
                    + " LEFT JOIN (select pc.pay_seq, count(*) as pay_cheque_count"
                        + " from fim_payment_cheques pc"
                        + " left join fim_cheques c on pc.cheque_seq = c.cheque_seq"
                        + " where pc.record_status not like @delete_status"
                        + " and c.record_status not like @delete_status"
                        + " and c.cheque_status = '2'"
                        + " group by pc.pay_seq"
                        + ") pc2 on pay.pay_seq = pc2.pay_seq"
                    + " LEFT JOIN (select pc.pay_seq, count(*) as pay_cheque_count"
                        + " from fim_payment_cheques pc"
                        + " left join fim_cheques c on pc.cheque_seq = c.cheque_seq"
                        + " where pc.record_status not like @delete_status"
                        + " and c.record_status not like @delete_status"
                        + " and c.cheque_status = '3'"
                        + " group by pc.pay_seq"
                        + ") pc3 on pay.pay_seq = pc3.pay_seq"

                    + " LEFT JOIN hrm_ps_employees emp ON pay.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq"
                    + " LEFT JOIN hrm_mp_work_areas area ON pay.work_area_seq = area.work_area_seq"
                    + " LEFT JOIN sys_record_status rs ON pay.record_status = rs.record_status"
                    + " WHERE pay.record_status not like @delete_status"
                    + " and pay.event_seq <> 26"
                    + (bgm_source_seq != null ? " and pay.bgm_source_seq = @bgm_source_seq" : "")
                    + (bgm_year != null ? " and pay.bgm_year = @bgm_year" : "")
                    + (work_area_seq != null ? " and pay.work_area_seq = @work_area_seq" : "")
                    + (event_seq != null ? " and pay.event_seq = @event_seq" : "")
                    + (direct_pay != null ? " and coalesce(p.direct_pay, '0') = @direct_pay" : "")
                    + " order by pay.pay_date desc";

                if (event_seq == 26)
                {
                    strSQL = ""//+= " union all"
                        + " SELECT v.vat_seq as pay_seq, v.bgm_source_seq, 0 as work_area_seq, v.bgm_year"
                        + ", 26 as event_seq"
                        + ", case when v.pay_seq is null"
                            + " then null"//dateadd(day, -1, dateadd(month, 1, Cast(cast(v.year_no - 543 as char) + '-' + cast(v.month_no as char) + '-1' as datetime)))"
                            + " else pay.pay_date end as pay_date"
                        + ", pay.real_pay_date, pay.ref_no, fim_money_requests.ref_no ref_no_money_request"
                        + ", pay.receipt_no, pay.receipt_name, concat('คำนวนภาษี ภพ.30 ประจำเดือน ', v.month_no) as pay_name, pay.method_seq"
                        + ", null as money_req_seq, null as petition_seq, null as petition_dtls_seq"
                        + ", pay.emp_seq, pay.contact_seq, null as bidder_seq, null as po_seq"
                        + ", case when v.pay_seq is null then v.next_month_vat_amount else pay.amount end as amount"
                        + ", null as vat_rate, null as vat, null as tax_rate, null as tax"
                        + ", null as post_pay_seq, null as tax_pay_seq"
                        + ", null as penalty_amount, null as redemption_amount"
                        + ", null as tax_invoice_id, null as tax_invoice_date"

                        + ", s.bgm_source_name, null as work_area_name, event.event_name"
                        + ", case when v.pay_seq is null then null else concat(np.prefix_short, emp.first_name, ' ', emp.last_name) end as emp_text"
                        + ", c.NameTh as contact_name, m.method_name"
                        + ", case when v.pay_seq is null then null"
                            + " else case when pay.contact_seq is not null then c.NameTh"
                                + " else concat(np.prefix_short, emp.first_name, ' ', emp.last_name)"
                                + " end"
                            + " end as payable_name"

                        + ", null as approved_by, null as approved_when, v.record_status"

                        + ", case when v.pay_seq is null then 'คำนวนภาษี'"
                            + " else case when pay.method_seq = 2"
                                + " then case when coalesce(pc3.pay_cheque_count, 0) > 0 then 'เช็คขึ้นเงินแล้ว'"
                                    + " when coalesce(pc2.pay_cheque_count, 0) > 0 then 'จ่ายเช็คแล้ว'"
                                    + " else rs.record_status_name"
                                    + " end"
                                + " else case when pay.real_pay_date is not null then 'จ่ายแล้ว'"
                                    + " else rs.record_status_name"
                                    + " end"
                                + " end"
                            + " end as record_status_name"
                        + ", v.created_by, v.created_when, v.updated_by, v.updated_when, null as from_bank_acc_id, null as to_bank_acc_id"
                        + ", v.month_no, v.year_no"
                        + " from fim_vat v"
                        + " left join fim_mas_events event on event.event_seq = 21"
                        + " left join bgm_mas_source s on v.bgm_source_seq = s.bgm_source_seq"
                        + " left join fim_payments pay on v.pay_seq = pay.pay_seq"
                        + " left join fim_money_requests ON pay.money_req_seq = fim_money_requests.money_req_seq"
                        + " LEFT JOIN (select pc.pay_seq, count(*) as pay_cheque_count"
                            + " from fim_payment_cheques pc"
                            + " left join fim_cheques c on pc.cheque_seq = c.cheque_seq"
                            + " where pc.record_status not like @delete_status"
                            + " and c.record_status not like @delete_status"
                            + " and c.cheque_status = '2'"
                            + " group by pc.pay_seq"
                            + ") pc2 on pay.pay_seq = pc2.pay_seq"
                        + " LEFT JOIN (select pc.pay_seq, count(*) as pay_cheque_count"
                            + " from fim_payment_cheques pc"
                            + " left join fim_cheques c on pc.cheque_seq = c.cheque_seq"
                            + " where pc.record_status not like @delete_status"
                            + " and c.record_status not like @delete_status"
                            + " and c.cheque_status = '3'"
                            + " group by pc.pay_seq"
                            + ") pc3 on pay.pay_seq = pc3.pay_seq"
                        + " LEFT JOIN fim_mas_methods m ON pay.method_seq = m.method_seq"
                        + " LEFT JOIN IcmMasContact c ON pay.contact_seq = c.ConId"
                        + " LEFT JOIN hrm_ps_employees emp ON pay.emp_seq = emp.emp_seq"
                        + " LEFT JOIN mas_name_prefixes np ON emp.name_prefix_seq = np.name_prefix_seq"
                        + " LEFT JOIN sys_record_status rs ON pay.record_status = rs.record_status"
                        + " WHERE v.record_status not like @delete_status"
                        + (bgm_source_seq != null ? " and v.bgm_source_seq = @bgm_source_seq" : "")
                        + (bgm_year != null ? " and v.bgm_year = @bgm_year" : "")
                        + " order by pay_date desc";
                }

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                DataRowCollection drc = dtb.Rows;
                if (dtb.Rows.Count > 0)
                {
                    for (int i = 0; i < dtb.Rows.Count; i++)
                    {
                        M_FIM_Payments info = new M_FIM_Payments();
                        info.pay_seq = Convert.ToInt32(drc[i]["pay_seq"]);
                        info.bgm_source_seq = Convert.ToInt32(drc[i]["bgm_source_seq"]);
                        info.work_area_seq = Convert.ToInt32(drc[i]["work_area_seq"]);
                        info.bgm_year = Convert.ToInt32(drc[i]["bgm_year"]);
                        info.event_seq = cn.ConvertNullInt(drc[i]["event_seq"]);
                        info.pay_date = cn.ConvertNullDateTime(drc[i]["pay_date"]) ?? new DateTime((cn.ConvertNullInt(drc[i]["year_no"]) ?? 0) - 543, cn.ConvertNullInt(drc[i]["month_no"]) ?? 0, 1).AddMonths(1).AddDays(-1);
                        info.real_pay_date = cn.ConvertNullDateTime(drc[i]["real_pay_date"]);
                        info.ref_no = drc[i]["ref_no"].ToString();
                        info.ref_no_money_request = drc[i]["ref_no_money_request"].ToString();
                        info.receipt_no = drc[i]["receipt_no"].ToString();
                        info.receipt_name = drc[i]["receipt_name"].ToString();
                        info.pay_name = drc[i]["pay_name"].ToString();
                        info.method_seq = cn.ConvertNullInt(drc[i]["method_seq"]);
                        info.money_req_seq = cn.ConvertNullInt(drc[i]["money_req_seq"]);
                        info.petition_seq = cn.ConvertNullInt(drc[i]["petition_seq"]);
                        info.petition_dtls_seq = cn.ConvertNullInt(drc[i]["petition_dtls_seq"]);
                        info.emp_seq = cn.ConvertNullInt(drc[i]["emp_seq"]);
                        info.contact_seq = cn.ConvertNullInt(drc[i]["contact_seq"]);
                        //info.contact_person_seq = cn.ConvertNullInt(drc[i]["contact_person_seq"]);
                        info.bidder_seq = cn.ConvertNullInt(drc[i]["bidder_seq"]);
                        info.po_seq = cn.ConvertNullInt(drc[i]["po_seq"]);
                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        info.penalty_amount = cn.ConvertNullDouble(drc[i]["penalty_amount"]);
                        info.redemption_amount = cn.ConvertNullDouble(drc[i]["redemption_amount"]);
                        info.vat_rate = cn.ConvertNullDouble(drc[i]["vat_rate"]);
                        info.vat = cn.ConvertNullDouble(drc[i]["vat"]);
                        info.tax_rate = cn.ConvertNullDouble(drc[i]["tax_rate"]);
                        info.tax = cn.ConvertNullDouble(drc[i]["tax"]);
                        info.post_pay_seq = cn.ConvertNullInt(drc[i]["post_pay_seq"]);
                        info.tax_pay_seq = cn.ConvertNullInt(drc[i]["tax_pay_seq"]);
                        info.tax_invoice_date = cn.ConvertNullDateTime(drc[i]["tax_invoice_date"]);
                        info.tax_invoice_id = drc[i]["tax_invoice_id"].ToString();

                        info.bgm_source_name = drc[i]["bgm_source_name"].ToString();
                        info.work_area_name = drc[i]["work_area_name"].ToString();
                        info.method_name = drc[i]["method_name"].ToString();
                        //info.money_req_name = drc[i]["money_req_name"].ToString();
                        info.from_bank_acc_id = cn.ConvertNullInt(drc[i]["from_bank_acc_id"].ToString());
                        info.to_bank_acc_id = cn.ConvertNullInt(drc[i]["to_bank_acc_id"].ToString());
                        //info.petition_dtls_no = drc[i]["petition_dtls_no"].ToString();
                        //info.emp_name = drc[i]["emp_name"].ToString();
                        info.contact_name = drc[i]["contact_name"].ToString();
                        //info.contact_person_name = drc[i]["contact_person_name"].ToString();
                        info.payable_name = drc[i]["payable_name"].ToString();
                        info.event_name = drc[i]["event_name"].ToString();

                        info.approved_by = cn.ConvertNullInt(drc[i]["approved_by"]);
                        info.approved_when = cn.ConvertNullDateTime(drc[i]["approved_when"]);

                        info.record_status = drc[i]["RECORD_STATUS"].ToString();
                        info.record_status_name = drc[i]["RECORD_STATUS_NAME"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["CREATED_BY"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["CREATED_WHEN"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["UPDATED_BY"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["UPDATED_WHEN"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_FIM_Payments> GetList(Nullable<int> bgm_source_seq
            , Nullable<int> bgm_year, Nullable<int> work_area_seq, Nullable<int> event_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_Payments> infoList = new List<M_FIM_Payments>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_year", bgm_year);
                dbManager.AddParameters(3, "@work_area_seq", work_area_seq);
                dbManager.AddParameters(4, "@event_seq", event_seq);

                strSQL = "SELECT transfer_seq"
                    + ", concat(coalesce(ref_no, ''), ' - ', coalesce(transfer_name, '')) as transfer_name"
                    + " FROM fim_transfers"
                    + " WHERE record_status not like @delete_status";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_Payments info = new M_FIM_Payments();
                        info.pay_seq = Convert.ToInt32(drc[i]["pay_seq"]);
                        info.pay_name = drc[i]["pay_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_FIM_Payments GetInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_FIM_Payments info = new M_FIM_Payments();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT pay.pay_seq, pay.bgm_source_seq, pay.work_area_seq, pay.bgm_year"
                    + ", pay.event_seq, pay.pay_date, pay.ref_no"
                    + ", case when pay.method_seq in (1,3) then pay.real_pay_date"
                        + " else case when pay.method_seq in (2) then cheq.pay_date"
                            + " else case when pay.method_seq in (4) then pip.pip_date"
                                + " else null"
                                + " end"
                            + " end"
                        + " end as real_pay_date"

                    + ", pay.receipt_no, pay.receipt_name, pay.pay_name, pay.method_seq"
                    + ", pay.money_req_seq, pay.petition_seq, pay.petition_dtls_seq"
                    + ", pay.emp_seq, pay.contact_seq, pay.bidder_seq, pay.po_seq"
                    + ", pay.amount, pay.vat_rate, pay.vat, pay.tax_rate, pay.tax"
                    + ", pay.post_pay_seq, pay.tax_pay_seq"
                    + ", pay.penalty_amount, pay.redemption_amount"
                    + ", pay.tax_invoice_date, pay.tax_invoice_id"

                    + ", s.bgm_source_name, area.work_area_name"
                    + ", concat(np.prefix_short, emp.first_name, ' ', emp.last_name)as emp_name"
                    + ", c.NameTh as contact_name"
                    + ", m.method_name"
                    + ", case when pay.contact_seq is not null then c.NameTh"
                            + " else concat(np.prefix_short, emp.first_name, ' ', emp.last_name)"
                        + " end as payable_name"

                    + ", mr.money_req_name, pt.petition_no, pt.ref_no as petition_ref_no"

                    + ", pay.approved_by, pay.approved_when, pay.record_status"
                    + ", case when pay.method_seq = 2"
                        + " then case when cheq.cheque_status = '3' then 'เช็คขึ้นเงินแล้ว'"
                            + " when cheq.cheque_status = '2' then 'จ่ายเช็คแล้ว'"
                            + " else rs.record_status_name"
                            + " end"
                        + " else case when pay.real_pay_date is not null then 'จ่ายแล้ว'"
                            + " else rs.record_status_name"
                            + " end"
                        + " end as record_status_name"
                    + ", pay.to_bank_acc_id, pay.from_bank_acc_id"
                    + ", pay.created_by, pay.created_when, pay.updated_by, pay.updated_when"
                    + " FROM fim_payments pay"
                    + " LEFT JOIN bgm_mas_source s ON pay.bgm_source_seq = s.bgm_source_seq"
                    + " LEFT JOIN fim_mas_methods m ON pay.method_seq = m.method_seq"
                    + " LEFT JOIN fim_money_requests mr ON pay.money_req_seq = mr.money_req_seq"
                    + " LEFT JOIN fim_payments_payinpack pip ON pay.pay_seq = pip.pay_seq"
                        + " and pip.record_status <> 'D'"

                    + " LEFT JOIN fim_petition_dtls ptd ON pay.petition_dtls_seq = ptd.petition_dtls_seq"
                    + " LEFT JOIN fim_petitions pt ON ptd.petition_seq = pt.petition_seq"
                    + " LEFT JOIN IcmMasContact c ON pay.contact_seq = c.ConId"
                    //+ " LEFT JOIN fim_mas_contact_persons cp ON pay.contact_person_seq = cp.contact_person_seq"
                    + " left join fim_payment_cheques pc on pay.pay_seq = pc.pay_seq"
                    + " left join fim_cheques cheq on pc.cheque_seq = cheq.cheque_seq"
                    //+ " LEFT JOIN (select pc.pay_seq, count(*) as pay_cheque_count"
                    //    + " from fim_payment_cheques pc"
                    //    + " left join fim_cheques c on pc.cheque_seq = c.cheque_seq"
                    //    + " where pc.record_status not like 'D'"
                    //    + " and c.record_status not like 'D'"
                    //    + " and c.cheque_status = '2'"
                    //    + " group by pc.pay_seq"
                    //    + ") pc2 on pay.pay_seq = pc2.pay_seq"
                    //+ " LEFT JOIN (select pc.pay_seq, count(*) as pay_cheque_count"
                    //    + " from fim_payment_cheques pc"
                    //    + " left join fim_cheques c on pc.cheque_seq = c.cheque_seq"
                    //    + " where pc.record_status not like 'D'"
                    //    + " and c.record_status not like 'D'"
                    //    + " and c.cheque_status = '3'"
                    //    + " group by pc.pay_seq"
                    //    + ") pc3 on pay.pay_seq = pc3.pay_seq"
                    //bidder_seq
                    //po_seq
                    //+ " LEFT JOIN fim_payments post ON pay.post_pay_seq = post.pay_seq"
                    //+ " LEFT JOIN fim_payments tax ON pay.tax_pay_seq = tax.pay_seq"

                    + " LEFT JOIN hrm_ps_employees emp ON pay.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq"
                    + " LEFT JOIN hrm_mp_work_areas area ON pay.work_area_seq = area.work_area_seq"
                    + " LEFT JOIN sys_record_status rs ON pay.record_status = rs.record_status"
                    + " WHERE pay.pay_seq = @seq";


                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.pay_seq = Convert.ToInt32(drc[0]["pay_seq"]);
                    info.bgm_source_seq = Convert.ToInt32(drc[0]["bgm_source_seq"]);
                    info.work_area_seq = Convert.ToInt32(drc[0]["work_area_seq"]);
                    info.bgm_year = Convert.ToInt32(drc[0]["bgm_year"]);
                    info.event_seq = cn.ConvertNullInt(drc[0]["event_seq"]);
                    info.pay_date = cn.ConvertNullDateTime(drc[0]["pay_date"]);
                    info.real_pay_date = cn.ConvertNullDateTime(drc[0]["real_pay_date"]);
                    info.ref_no = drc[0]["ref_no"].ToString();
                    info.receipt_no = drc[0]["receipt_no"].ToString();
                    info.receipt_name = drc[0]["receipt_name"].ToString();
                    info.pay_name = drc[0]["pay_name"].ToString();
                    info.method_seq = cn.ConvertNullInt(drc[0]["method_seq"]);
                    info.money_req_seq = cn.ConvertNullInt(drc[0]["money_req_seq"]);
                    info.petition_seq = cn.ConvertNullInt(drc[0]["petition_seq"]);
                    info.petition_dtls_seq = cn.ConvertNullInt(drc[0]["petition_dtls_seq"]);
                    info.emp_seq = cn.ConvertNullInt(drc[0]["emp_seq"]);
                    info.contact_seq = cn.ConvertNullInt(drc[0]["contact_seq"]);
                    info.bidder_seq = cn.ConvertNullInt(drc[0]["bidder_seq"]);
                    info.po_seq = cn.ConvertNullInt(drc[0]["po_seq"]);
                    info.amount = cn.ConvertNullDouble(drc[0]["amount"]);
                    info.penalty_amount = cn.ConvertNullDouble(drc[0]["penalty_amount"]);
                    info.redemption_amount = cn.ConvertNullDouble(drc[0]["redemption_amount"]);
                    info.vat_rate = cn.ConvertNullDouble(drc[0]["vat_rate"]);
                    info.vat = cn.ConvertNullDouble(drc[0]["vat"]);
                    info.tax_rate = cn.ConvertNullDouble(drc[0]["tax_rate"]);
                    info.tax = cn.ConvertNullDouble(drc[0]["tax"]);
                    info.post_pay_seq = cn.ConvertNullInt(drc[0]["post_pay_seq"]);
                    info.tax_pay_seq = cn.ConvertNullInt(drc[0]["tax_pay_seq"]);
                    info.to_bank_acc_id = cn.ConvertNullInt(drc[0]["to_bank_acc_id"]);
                    info.from_bank_acc_id = cn.ConvertNullInt(drc[0]["from_bank_acc_id"]);
                    info.bgm_source_name = drc[0]["bgm_source_name"].ToString();
                    info.work_area_name = drc[0]["work_area_name"].ToString();
                    info.method_name = drc[0]["method_name"].ToString();
                    info.money_req_name = drc[0]["money_req_name"].ToString();
                    info.petition_no = drc[0]["petition_no"].ToString();
                    //info.petition_ref_no = drc[0]["petition_ref_no"].ToString();
                    info.emp_name = drc[0]["emp_name"].ToString();
                    info.contact_name = drc[0]["contact_name"].ToString();
                    //info.contact_person_name = drc[0]["contact_person_name"].ToString();
                    info.payable_name = drc[0]["payable_name"].ToString();
                    info.tax_invoice_date = cn.ConvertNullDateTime(drc[0]["tax_invoice_date"]);
                    info.tax_invoice_id = drc[0]["tax_invoice_id"].ToString();
                    info.approved_by = cn.ConvertNullInt(drc[0]["approved_by"]);
                    info.approved_when = cn.ConvertNullDateTime(drc[0]["approved_when"]);

                    info.record_status = drc[0]["RECORD_STATUS"].ToString();
                    info.record_status_name = drc[0]["RECORD_STATUS_NAME"].ToString();
                    info.created_by = cn.ConvertNullInt(drc[0]["CREATED_BY"]);
                    info.created_when = cn.ConvertNullDateTime(drc[0]["CREATED_WHEN"]);
                    info.updated_by = cn.ConvertNullInt(drc[0]["UPDATED_BY"]);
                    info.updated_when = cn.ConvertNullDateTime(drc[0]["UPDATED_WHEN"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public M_FIM_Payments GetInfoFromMoneyReqSeq(int money_req_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_FIM_Payments info = new M_FIM_Payments();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@money_req_seq", money_req_seq);

                strSQL = "SELECT pay.pay_seq, pay.bgm_source_seq, pay.work_area_seq, pay.bgm_year"
                    + ", pay.event_seq, pay.pay_date, pay.real_pay_date, pay.ref_no"
                    + ", pay.receipt_no, pay.receipt_name, pay.pay_name, pay.method_seq"
                    + ", pay.money_req_seq, pay.petition_seq, pay.petition_dtls_seq"
                    + ", pay.emp_seq, pay.contact_seq, pay.contact_person_seq, pay.bidder_seq, pay.po_seq"
                    + ", pay.amount, pay.vat_rate, pay.vat, pay.tax_rate, pay.tax"
                    + ", pay.post_pay_seq, pay.tax_pay_seq"

                    + ", s.bgm_source_name, area.work_area_name"
                    + ", concat(np.prefix_short, emp.first_name, ' ', emp.last_name)as emp_name"
                    + ", c.NameTh as contact_name"
                    + ", m.method_name"
                    + ", case when pay.contact_seq is not null then c.NameTh"
                            + " else concat(np.prefix_short, emp.first_name, ' ', emp.last_name)"
                        + " end as payable_name"

                    + ", mr.money_req_name, pt.petition_no, pt.ref_no as petition_ref_no"

                    + ", pay.approved_by, pay.approved_when, pay.record_status"
                    + ", case when pay.method_seq = 2"
                        + " then case when coalesce(pc3.pay_cheque_count, 0) > 0 then 'เช็คขึ้นเงินแล้ว'"
                            + " when coalesce(pc2.pay_cheque_count, 0) > 0 then 'จ่ายเช็คแล้ว'"
                            + " else rs.record_status_name"
                            + " end"
                        + " else case when pay.real_pay_date is not null then 'จ่ายแล้ว'"
                            + " else rs.record_status_name"
                            + " end"
                        + " end as record_status_name"
                    + ", pay.created_by, pay.created_when, pay.updated_by, pay.updated_when"
                    + " FROM fim_payments pay"
                    + " LEFT JOIN bgm_mas_source s ON pay.bgm_source_seq = s.bgm_source_seq"
                    + " LEFT JOIN fim_mas_methods m ON pay.method_seq = m.method_seq"
                    + " LEFT JOIN fim_money_requests mr ON pay.money_req_seq = mr.money_req_seq"

                    + " LEFT JOIN fim_petition_dtls ptd ON pay.petition_dtls_seq = ptd.petition_dtls_seq"
                    + " LEFT JOIN fim_petitions pt ON ptd.petition_seq = pt.petition_seq"
                    + " LEFT JOIN IcmMasContact c ON pay.contact_seq = c.ConId"
                    //+ " LEFT JOIN fim_mas_contact_persons cp ON pay.contact_person_seq = cp.contact_person_seq"

                    + " LEFT JOIN (select pc.pay_seq, count(*) as pay_cheque_count"
                        + " from fim_payment_cheques pc"
                        + " left join fim_cheques c on pc.cheque_seq = c.cheque_seq"
                        + " where pc.record_status not like @delete_status"
                        + " and c.record_status not like @delete_status"
                        + " and c.cheque_status = '2'"
                        + " group by pc.pay_seq"
                        + ") pc2 on pay.pay_seq = pc2.pay_seq"
                    + " LEFT JOIN (select pc.pay_seq, count(*) as pay_cheque_count"
                        + " from fim_payment_cheques pc"
                        + " left join fim_cheques c on pc.cheque_seq = c.cheque_seq"
                        + " where pc.record_status not like @delete_status"
                        + " and c.record_status not like @delete_status"
                        + " and c.cheque_status = '3'"
                        + " group by pc.pay_seq"
                        + ") pc3 on pay.pay_seq = pc3.pay_seq"

                    //bidder_seq
                    //po_seq
                    //+ " LEFT JOIN fim_payments post ON pay.post_pay_seq = post.pay_seq"
                    //+ " LEFT JOIN fim_payments tax ON pay.tax_pay_seq = tax.pay_seq"

                    + " LEFT JOIN hrm_ps_employees emp ON pay.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq"
                    + " LEFT JOIN hrm_mp_work_areas area ON pay.work_area_seq = area.work_area_seq"
                    + " LEFT JOIN sys_record_status rs ON pay.record_status = rs.record_status"
                    + " WHERE pay.money_req_seq = @money_req_seq"
                    + " and pay.record_status not like @delete_status";


                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.pay_seq = Convert.ToInt32(drc[0]["pay_seq"]);
                    info.bgm_source_seq = Convert.ToInt32(drc[0]["bgm_source_seq"]);
                    info.work_area_seq = Convert.ToInt32(drc[0]["work_area_seq"]);
                    info.bgm_year = Convert.ToInt32(drc[0]["bgm_year"]);
                    info.event_seq = cn.ConvertNullInt(drc[0]["event_seq"]);
                    info.pay_date = cn.ConvertNullDateTime(drc[0]["pay_date"]);
                    info.real_pay_date = cn.ConvertNullDateTime(drc[0]["real_pay_date"]);
                    info.ref_no = drc[0]["ref_no"].ToString();
                    info.receipt_no = drc[0]["receipt_no"].ToString();
                    info.receipt_name = drc[0]["receipt_name"].ToString();
                    info.pay_name = drc[0]["pay_name"].ToString();
                    info.method_seq = cn.ConvertNullInt(drc[0]["method_seq"]);
                    info.money_req_seq = cn.ConvertNullInt(drc[0]["money_req_seq"]);
                    info.petition_seq = cn.ConvertNullInt(drc[0]["petition_seq"]);
                    info.petition_dtls_seq = cn.ConvertNullInt(drc[0]["petition_dtls_seq"]);
                    info.emp_seq = cn.ConvertNullInt(drc[0]["emp_seq"]);
                    info.contact_seq = cn.ConvertNullInt(drc[0]["contact_seq"]);
                    info.bidder_seq = cn.ConvertNullInt(drc[0]["bidder_seq"]);
                    info.po_seq = cn.ConvertNullInt(drc[0]["po_seq"]);
                    info.amount = cn.ConvertNullDouble(drc[0]["amount"]);
                    info.vat_rate = cn.ConvertNullDouble(drc[0]["vat_rate"]);
                    info.vat = cn.ConvertNullDouble(drc[0]["vat"]);
                    info.tax_rate = cn.ConvertNullDouble(drc[0]["tax_rate"]);
                    info.tax = cn.ConvertNullDouble(drc[0]["tax"]);
                    info.post_pay_seq = cn.ConvertNullInt(drc[0]["post_pay_seq"]);
                    info.tax_pay_seq = cn.ConvertNullInt(drc[0]["tax_pay_seq"]);

                    info.bgm_source_name = drc[0]["bgm_source_name"].ToString();
                    info.work_area_name = drc[0]["work_area_name"].ToString();
                    info.method_name = drc[0]["method_name"].ToString();
                    info.money_req_name = drc[0]["money_req_name"].ToString();
                    info.petition_no = drc[0]["petition_no"].ToString();
                    //info.petition_ref_no = drc[0]["petition_ref_no"].ToString();
                    info.emp_name = drc[0]["emp_name"].ToString();
                    info.contact_name = drc[0]["contact_name"].ToString();
                    //info.contact_person_name = drc[0]["contact_person_name"].ToString();
                    info.payable_name = drc[0]["payable_name"].ToString();

                    info.approved_by = cn.ConvertNullInt(drc[0]["approved_by"]);
                    info.approved_when = cn.ConvertNullDateTime(drc[0]["approved_when"]);

                    info.record_status = drc[0]["RECORD_STATUS"].ToString();
                    info.record_status_name = drc[0]["RECORD_STATUS_NAME"].ToString();
                    info.created_by = cn.ConvertNullInt(drc[0]["CREATED_BY"]);
                    info.created_when = cn.ConvertNullDateTime(drc[0]["CREATED_WHEN"]);
                    info.updated_by = cn.ConvertNullInt(drc[0]["UPDATED_BY"]);
                    info.updated_when = cn.ConvertNullDateTime(drc[0]["UPDATED_WHEN"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public IList<M_FIM_Payments> GetListFromPetition(int petition_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_Payments> infoList = new List<M_FIM_Payments>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@seq", petition_seq);

                strSQL = "SELECT pay.pay_seq, pay.bgm_source_seq, pay.work_area_seq, pay.bgm_year"
                    + ", pay.event_seq, pay.pay_date, pay.real_pay_date, pay.ref_no"
                    + ", pay.receipt_no, pay.receipt_name, pay.pay_name, pay.method_seq"
                    + ", pay.money_req_seq, pay.petition_seq, pay.petition_dtls_seq"
                    + ", pay.emp_seq, pay.contact_seq, pay.bidder_seq, pay.po_seq"
                    + ", pay.amount, pay.vat_rate, pay.vat, pay.tax_rate, pay.tax"
                    + ", pay.post_pay_seq, pay.tax_pay_seq"

                    + ", s.bgm_source_name, area.work_area_name"
                    + ", concat(np.prefix_short, emp.first_name, ' ', emp.last_name)as emp_text"
                    + ", c.NameTh"
                    + ", case when pay.method_seq = '1' then 'เงินสด'"
                        + " else case when pay.method_seq = '2' then 'เช็ค'"
                            + " else case when pay.method_seq = '3' then 'โอน'"
                                + " else 'ดราฟท์'"
                                + " end"
                            + " end"
                        + " end as method_name"
                    + ", case when pay.contact_seq is not null then c.NameTh"
                            + " else concat(np.prefix_short, emp.first_name, ' ', emp.last_name)"
                        + " end as payable_name"

                    + ", pay.approved_by, pay.approved_when, pay.record_status, rs.record_status_name"
                    + ", pay.created_by, pay.created_when, pay.updated_by, pay.updated_when"
                    + " FROM fim_payments pay"
                    + " LEFT JOIN bgm_mas_source s ON pay.bgm_source_seq = s.bgm_source_seq"
                    //+ " LEFT JOIN fim_money_requests mr ON pay.money_req_seq = mr.money_req_seq"
                    //+ " LEFT JOIN fim_petitions pt ON pay.petition_seq = pt.petition_seq"
                    //+ " LEFT JOIN fim_petitions ptd ON pay.petition_dtls_seq = ptd.petition_dtls_seq"
                    + " LEFT JOIN IcmMasContact c ON pay.contact_seq = c.ConId"
                    //+ " LEFT JOIN fim_mas_contact_persons cp ON pay.contact_person_seq = cp.contact_person_seq"
                    //bidder_seq
                    //po_seq
                    //+ " LEFT JOIN fim_payments post ON pay.post_pay_seq = post.pay_seq"
                    //+ " LEFT JOIN fim_payments tax ON pay.tax_pay_seq = tax.pay_seq"

                    + " LEFT JOIN hrm_ps_employees emp ON pay.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq"
                    + " LEFT JOIN hrm_mp_work_areas area ON pay.work_area_seq = area.work_area_seq"
                    + " LEFT JOIN sys_record_status rs ON pay.record_status = rs.record_status"
                    + " WHERE pay.petition_seq = @seq";


                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_Payments info = new M_FIM_Payments();
                        info.pay_seq = Convert.ToInt32(drc[i]["pay_seq"]);
                        info.bgm_source_seq = Convert.ToInt32(drc[i]["bgm_source_seq"]);
                        info.work_area_seq = Convert.ToInt32(drc[i]["work_area_seq"]);
                        info.bgm_year = Convert.ToInt32(drc[i]["bgm_year"]);
                        info.event_seq = cn.ConvertNullInt(drc[i]["event_seq"]);
                        info.pay_date = cn.ConvertNullDateTime(drc[i]["pay_date"]);
                        info.real_pay_date = cn.ConvertNullDateTime(drc[i]["real_pay_date"]);
                        info.ref_no = drc[i]["ref_no"].ToString();
                        info.receipt_no = drc[i]["receipt_no"].ToString();
                        info.receipt_name = drc[i]["receipt_name"].ToString();
                        info.pay_name = drc[i]["pay_name"].ToString();
                        info.method_seq = cn.ConvertNullInt(drc[i]["method_seq"]);
                        info.money_req_seq = cn.ConvertNullInt(drc[i]["money_req_seq"]);
                        info.petition_seq = cn.ConvertNullInt(drc[i]["petition_seq"]);
                        info.petition_dtls_seq = cn.ConvertNullInt(drc[i]["petition_dtls_seq"]);
                        info.emp_seq = cn.ConvertNullInt(drc[i]["emp_seq"]);
                        info.contact_seq = cn.ConvertNullInt(drc[i]["contact_seq"]);
                        info.bidder_seq = cn.ConvertNullInt(drc[i]["bidder_seq"]);
                        info.po_seq = cn.ConvertNullInt(drc[i]["po_seq"]);
                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        info.vat_rate = cn.ConvertNullDouble(drc[i]["vat_rate"]);
                        info.vat = cn.ConvertNullDouble(drc[i]["vat"]);
                        info.tax_rate = cn.ConvertNullDouble(drc[i]["tax_rate"]);
                        info.tax = cn.ConvertNullDouble(drc[i]["tax"]);
                        info.post_pay_seq = cn.ConvertNullInt(drc[i]["post_pay_seq"]);
                        info.tax_pay_seq = cn.ConvertNullInt(drc[i]["tax_pay_seq"]);

                        info.bgm_source_name = drc[i]["bgm_source_name"].ToString();
                        info.work_area_name = drc[i]["work_area_name"].ToString();
                        info.method_name = drc[i]["method_name"].ToString();
                        info.money_req_name = drc[i]["money_req_name"].ToString();
                        info.petition_no = drc[i]["petition_no"].ToString();
                        info.petition_dtls_no = drc[i]["petition_dtls_no"].ToString();
                        info.emp_name = drc[i]["emp_name"].ToString();
                        info.contact_name = drc[i]["NameTh"].ToString();
                        //info.contact_person_name = drc[i]["contact_person_name"].ToString();
                        info.payable_name = drc[i]["payable_name"].ToString();

                        info.approved_by = cn.ConvertNullInt(drc[i]["approved_by"]);
                        info.approved_when = cn.ConvertNullDateTime(drc[i]["approved_when"]);

                        info.record_status = drc[i]["RECORD_STATUS"].ToString();
                        info.record_status_name = drc[i]["RECORD_STATUS_NAME"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["CREATED_BY"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["CREATED_WHEN"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["UPDATED_BY"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["UPDATED_WHEN"]);

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_FIM_Payments> GetNotPayTax(int? TaxPaySeq, string strBgmSourceSeq, string strBgmYear, string strContactType)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_Payments> infoList = new List<M_FIM_Payments>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@approve_status", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(1, "@TaxPaySeq", TaxPaySeq);
                dbManager.AddParameters(2, "@BgmSourceSeq", strBgmSourceSeq);
                dbManager.AddParameters(3, "@BgmYear", strBgmYear);
                dbManager.AddParameters(4, "@ContactType", strContactType);

                strSQL = "select p.*"
                    + ", m.method_name, event.event_name"
                    + ", case when p.contact_seq is not null then c.NameTh"
                            + " else concat(np.prefix_short, emp.first_name, ' ', emp.last_name)"
                        + " end as payable_name"
                    + ", chq.pay_date as cheque_pay_date"
                    + ", pip.pip_date as pip_date"
                    + " from fim_payments p"
                    + " LEFT JOIN fim_mas_events event ON p.event_seq = event.event_seq"
                    + " LEFT JOIN fim_mas_methods m ON p.method_seq = m.method_seq"
                    + " LEFT JOIN IcmMasContact c ON p.contact_seq = c.ConId"
                    + " LEFT JOIN hrm_ps_employees emp ON p.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq"

                    + " left join fim_payment_cheques pc on p.pay_seq = pc.pay_seq"
                        + " and pc.record_status not like 'D'"
                    + " left join fim_cheques chq on pc.cheque_seq = chq.cheque_seq"
                        + " and chq.record_status not in ('D', 'C')"
                    + " left join fim_payments_payinpack pip on pip.pay_seq = p.pay_seq"
                        + " and pip.record_status <> 'D'"
                    + " where p.record_status = @approve_status"
                    + (TaxPaySeq != null ? " and (p.tax_pay_seq is null or p.tax_pay_seq = @TaxPaySeq)" : " and p.tax_pay_seq is null")
                    + (!string.IsNullOrEmpty(strBgmSourceSeq) ? " and p.bgm_source_seq = @BgmSourceSeq" : "")
                    + (!string.IsNullOrEmpty(strBgmYear) ? " and p.bgm_year = @BgmYear" : "")
                    + (!string.IsNullOrEmpty(strContactType) ? strContactType == "47" ? " and c.ConGrpId = 47" : " and c.ConGrpId <> 47" : "")
                    + " and coalesce(p.tax, 0) > 0"
                    + " and ((p.method_seq in (1,3) and p.real_pay_date is not null)"
                    + " or (p.method_seq = 2 and chq.pay_date is not null)"
                    + " or (p.method_seq = 4 and pip.pip_date is not null))";

                //strSQL = "select p.*"
                //    + ", m.method_name, event.event_name"
                //    + ", case when p.contact_seq is not null then c.NameTh"
                //            + " else concat(np.prefix_short, emp.first_name, ' ', emp.last_name)"
                //        + " end as payable_name"
                //    + " from fim_payments p"
                //    + " LEFT JOIN fim_mas_events event ON p.event_seq = event.event_seq"
                //    + " LEFT JOIN fim_mas_methods m ON p.method_seq = m.method_seq"
                //    + " LEFT JOIN IcmMasContact c ON p.contact_seq = c.ConId"
                //    + " LEFT JOIN hrm_ps_employees emp ON p.emp_seq = emp.emp_seq"
                //    + " LEFT JOIN mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq"

                //    + " left join fim_payment_cheques pc on p.pay_seq = pc.pay_seq"
                //    + " left join fim_cheques chq on pc.cheque_seq = chq.cheque_seq"
                //    + " where p.record_status = @approve_status"
                //    + (TaxPaySeq != null ? " and (p.tax_pay_seq is null or p.tax_pay_seq = @TaxPaySeq)" : " and p.tax_pay_seq is null")
                //    + (!string.IsNullOrEmpty(strBgmSourceSeq) ? " and p.bgm_source_seq = @BgmSourceSeq" : "")
                //    + (!string.IsNullOrEmpty(strBgmYear) ? " and p.bgm_year = @BgmYear" : "")
                //    + (!string.IsNullOrEmpty(strContactType) ? strContactType == "47" ? " and c.ConGrpId = 47" : " and c.ConGrpId <> 47" : "")
                //    + " and coalesce(p.tax, 0) > 0"
                //    + " and ((p.method_seq <> 2 and p.real_pay_date is not null)"
                //    + " or (p.method_seq = 2 and chq.pay_date is not null))";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        int nMethodSeq = cn.ConvertNullInt(drc[i]["method_seq"]) ?? 0;

                        M_FIM_Payments info = new M_FIM_Payments();
                        info.pay_seq = Convert.ToInt32(drc[i]["pay_seq"]);
                        info.bgm_source_seq = Convert.ToInt32(drc[i]["bgm_source_seq"]);
                        info.work_area_seq = Convert.ToInt32(drc[i]["work_area_seq"]);
                        info.bgm_year = Convert.ToInt32(drc[i]["bgm_year"]);
                        info.event_seq = cn.ConvertNullInt(drc[i]["event_seq"]);
                        info.pay_date = (nMethodSeq == 1 || nMethodSeq == 3 ? cn.ConvertNullDateTime(drc[i]["real_pay_date"]) : nMethodSeq == 2 ?
                            cn.ConvertNullDateTime(drc[i]["cheque_pay_date"]) : cn.ConvertNullDateTime(drc[i]["pip_date"]));
                        info.real_pay_date = cn.ConvertNullDateTime(drc[i]["real_pay_date"]);
                        info.ref_no = drc[i]["ref_no"].ToString();
                        info.receipt_no = drc[i]["receipt_no"].ToString();
                        info.receipt_name = drc[i]["receipt_name"].ToString();
                        info.pay_name = drc[i]["pay_name"].ToString();
                        info.method_seq = cn.ConvertNullInt(drc[i]["method_seq"]);
                        info.money_req_seq = cn.ConvertNullInt(drc[i]["money_req_seq"]);
                        info.petition_seq = cn.ConvertNullInt(drc[i]["petition_seq"]);
                        info.petition_dtls_seq = cn.ConvertNullInt(drc[i]["petition_dtls_seq"]);
                        info.emp_seq = cn.ConvertNullInt(drc[i]["emp_seq"]);
                        info.contact_seq = cn.ConvertNullInt(drc[i]["contact_seq"]);
                        info.bidder_seq = cn.ConvertNullInt(drc[i]["bidder_seq"]);
                        info.po_seq = cn.ConvertNullInt(drc[i]["po_seq"]);
                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        info.vat_rate = cn.ConvertNullDouble(drc[i]["vat_rate"]);
                        info.vat = cn.ConvertNullDouble(drc[i]["vat"]);
                        info.tax_rate = cn.ConvertNullDouble(drc[i]["tax_rate"]);
                        info.tax = cn.ConvertNullDouble(drc[i]["tax"]);
                        info.post_pay_seq = cn.ConvertNullInt(drc[i]["post_pay_seq"]);
                        info.tax_pay_seq = cn.ConvertNullInt(drc[i]["tax_pay_seq"]);
                        info.tax_invoice_date = cn.ConvertNullDateTime(drc[i]["tax_invoice_date"]);
                        info.tax_invoice_id = drc[i]["tax_invoice_id"].ToString();

                        //info.bgm_source_name = drc[i]["bgm_source_name"].ToString();
                        //info.work_area_name = drc[i]["work_area_name"].ToString();
                        info.method_name = drc[i]["method_name"].ToString();
                        info.event_name = drc[i]["event_name"].ToString();
                        //info.money_req_name = drc[i]["money_req_name"].ToString();
                        //info.petition_no = drc[i]["petition_no"].ToString();
                        //info.petition_dtls_no = drc[i]["petition_dtls_no"].ToString();
                        //info.emp_name = drc[i]["emp_name"].ToString();
                        //info.contact_name = drc[i]["contact_name"].ToString();
                        info.payable_name = drc[i]["payable_name"].ToString();

                        info.approved_by = cn.ConvertNullInt(drc[i]["approved_by"]);
                        info.approved_when = cn.ConvertNullDateTime(drc[i]["approved_when"]);

                        info.record_status = drc[i]["RECORD_STATUS"].ToString();
                        //info.record_status_name = drc[i]["RECORD_STATUS_NAME"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["CREATED_BY"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["CREATED_WHEN"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["UPDATED_BY"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["UPDATED_WHEN"]);

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        //public string SelectRecentInsertedRoleSeq()
        //{
        //    string strSQL;
        //    string strResult;

        //    try
        //    {
        //        dbManager.Open();

        //        strSQL = "select scope_identity() as last_seq";

        //        strResult = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0]["last_seq"].ToString();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return strResult;
        //}

        public string Insert(M_FIM_Payments info)
        {
            string strSQL;
            string strSeq = "";
            try
            {
                //string strRefNo = GetNextRefNo(info.bgm_year, info.bgm_source_seq);
                dbManager.Open();
                dbManager.CreateParameters(36);
                dbManager.BeginTransaction();

                dbManager.AddParameters(0, "@pay_seq", info.pay_seq);
                dbManager.AddParameters(1, "@bgm_source_seq", info.bgm_source_seq);
                dbManager.AddParameters(2, "@work_area_seq", info.work_area_seq);
                dbManager.AddParameters(3, "@bgm_year", info.bgm_year);
                dbManager.AddParameters(4, "@event_seq", info.event_seq);
                dbManager.AddParameters(5, "@pay_date", info.pay_date);
                dbManager.AddParameters(6, "@real_pay_date", info.real_pay_date);
                dbManager.AddParameters(7, "@ref_no", info.ref_no);
                dbManager.AddParameters(8, "@receipt_no", info.receipt_no);
                dbManager.AddParameters(9, "@receipt_name", info.receipt_name);
                dbManager.AddParameters(10, "@pay_name", info.pay_name);
                dbManager.AddParameters(11, "@method_seq", info.method_seq);
                dbManager.AddParameters(12, "@money_req_seq", info.money_req_seq);
                dbManager.AddParameters(13, "@petition_seq", info.petition_seq);
                dbManager.AddParameters(14, "@petition_dtls_seq", info.petition_dtls_seq);
                dbManager.AddParameters(15, "@emp_seq", info.emp_seq);
                dbManager.AddParameters(16, "@contact_seq", info.contact_seq);
                dbManager.AddParameters(17, "@contact_person_seq", "");
                dbManager.AddParameters(18, "@bidder_seq", info.bidder_seq);
                dbManager.AddParameters(19, "@po_seq", info.po_seq);
                dbManager.AddParameters(20, "@amount", info.amount);
                dbManager.AddParameters(21, "@vat_rate", info.vat_rate);
                dbManager.AddParameters(22, "@vat", info.vat);
                dbManager.AddParameters(23, "@tax_rate", info.tax_rate);
                dbManager.AddParameters(24, "@tax", info.tax);
                dbManager.AddParameters(25, "@post_pay_seq", info.post_pay_seq);
                dbManager.AddParameters(26, "@tax_pay_seq", info.tax_pay_seq);
                dbManager.AddParameters(27, "@tran_grp_seq", info.tran_grp_seq);
                dbManager.AddParameters(28, "@remarks", info.remarks);

                dbManager.AddParameters(29, "@record_status", (string.IsNullOrEmpty(info.record_status) ? SystemInfo._WorkRecordStatus : info.record_status));
                dbManager.AddParameters(30, "@created_by", info.created_by);
                dbManager.AddParameters(31, "@created_when", info.created_when);
                dbManager.AddParameters(32, "@from_bank_acc_id", info.from_bank_acc_id);
                dbManager.AddParameters(33, "@to_bank_acc_id", info.to_bank_acc_id);
                dbManager.AddParameters(34, "@tax_invoice_date", info.tax_invoice_date);
                dbManager.AddParameters(35, "@tax_invoice_id", info.tax_invoice_id);

                strSQL = "INSERT INTO fim_payments"
                    + "(bgm_source_seq, work_area_seq, bgm_year, event_seq"
                    + ", pay_date, real_pay_date, ref_no, receipt_no, receipt_name, pay_name"
                    + ", method_seq, money_req_seq, petition_seq, petition_dtls_seq, emp_seq"
                    + ", contact_seq, bidder_seq, po_seq, amount, vat_rate"
                    + ", vat, tax_rate, tax, post_pay_seq, tax_pay_seq, tran_grp_seq, remarks"
                    + ", record_status, created_by, created_when, from_bank_acc_id, to_bank_acc_id"
                    + ", tax_invoice_date, tax_invoice_id)"
                    + " VALUES(@bgm_source_seq, @work_area_seq, @bgm_year, @event_seq"
                    + ", @pay_date, @real_pay_date, @ref_no, @receipt_no, @receipt_name, @pay_name"
                    + ", @method_seq, @money_req_seq, @petition_seq, @petition_dtls_seq, @emp_seq"
                    + ", @contact_seq, @bidder_seq, @po_seq, @amount, @vat_rate"
                    + ", @vat, @tax_rate, @tax, @post_pay_seq, @tax_pay_seq, @tran_grp_seq, @remarks"
                    + ", @record_status, @created_by, @created_when, @from_bank_acc_id, @to_bank_acc_id"
                    + ", @tax_invoice_date, @tax_invoice_id);"
                    + " select scope_identity()";
                strSeq = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();

                dbManager.CommitTransaction();
                // return strSeq;
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

            return strSeq;
        }

        public void Update(M_FIM_Payments info)
        {
            string strSQL;
            try
            {
                if (string.IsNullOrEmpty(info.ref_no))
                {
                    info.ref_no = GetInfo(info.pay_seq).ref_no;
                }

                dbManager.Open();
                dbManager.CreateParameters(35);

                dbManager.AddParameters(0, "@pay_seq", info.pay_seq);
                dbManager.AddParameters(1, "@bgm_source_seq", info.bgm_source_seq);
                dbManager.AddParameters(2, "@work_area_seq", info.work_area_seq);
                dbManager.AddParameters(3, "@bgm_year", info.bgm_year);
                dbManager.AddParameters(4, "@event_seq", info.event_seq);
                dbManager.AddParameters(5, "@pay_date", info.pay_date);
                dbManager.AddParameters(6, "@real_pay_date", info.real_pay_date);
                dbManager.AddParameters(7, "@ref_no", info.ref_no);
                dbManager.AddParameters(8, "@receipt_no", info.receipt_no);
                dbManager.AddParameters(9, "@receipt_name", info.receipt_name);
                dbManager.AddParameters(10, "@pay_name", info.pay_name);
                dbManager.AddParameters(11, "@method_seq", info.method_seq);
                dbManager.AddParameters(12, "@money_req_seq", info.money_req_seq);
                dbManager.AddParameters(13, "@petition_seq", info.petition_seq);
                dbManager.AddParameters(14, "@petition_dtls_seq", info.petition_dtls_seq);
                dbManager.AddParameters(15, "@emp_seq", info.emp_seq);
                dbManager.AddParameters(16, "@contact_seq", info.contact_seq);
                dbManager.AddParameters(17, "@contact_person_seq", "");
                dbManager.AddParameters(18, "@bidder_seq", info.bidder_seq);
                dbManager.AddParameters(19, "@po_seq", info.po_seq);
                dbManager.AddParameters(20, "@amount", info.amount);
                dbManager.AddParameters(21, "@vat_rate", info.vat_rate);
                dbManager.AddParameters(22, "@vat", info.vat);
                dbManager.AddParameters(23, "@tax_rate", info.tax_rate);
                dbManager.AddParameters(24, "@tax", info.tax);
                dbManager.AddParameters(25, "@post_pay_seq", info.post_pay_seq);
                dbManager.AddParameters(26, "@tax_pay_seq", info.tax_pay_seq);
                dbManager.AddParameters(27, "@tran_grp_seq", info.tran_grp_seq);
                dbManager.AddParameters(28, "@remarks", info.remarks);

                dbManager.AddParameters(29, "@updated_by", info.updated_by);
                dbManager.AddParameters(30, "@updated_when", info.updated_when);
                dbManager.AddParameters(31, "@from_bank_acc_id", info.from_bank_acc_id);
                dbManager.AddParameters(32, "@to_bank_acc_id", info.to_bank_acc_id);
                dbManager.AddParameters(33, "@tax_invoice_date", info.tax_invoice_date);
                dbManager.AddParameters(34, "@tax_invoice_id", info.tax_invoice_id);

                strSQL = "UPDATE fim_payments"
                    + " set bgm_source_seq = @bgm_source_seq"
                    + ", work_area_seq = @work_area_seq"
                    + ", bgm_year = @bgm_year"
                    + ", event_seq = @event_seq"
                    + ", pay_date = @pay_date"
                    + ", real_pay_date = @real_pay_date"
                    + ", ref_no = @ref_no"
                    + ", receipt_no = @receipt_no"
                    + ", receipt_name = @receipt_name"
                    + ", pay_name = @pay_name"
                    + ", method_seq = @method_seq"
                    + ", money_req_seq = @money_req_seq"
                    + ", petition_seq = @petition_seq"
                    + ", petition_dtls_seq = @petition_dtls_seq"
                    + ", emp_seq = @emp_seq"
                    + ", contact_seq = @contact_seq"
                    //+ ", contact_person_seq = @contact_person_seq"
                    + ", bidder_seq = @bidder_seq"
                    + ", po_seq = @po_seq"
                    + ", amount = @amount"
                    + ", vat_rate = @vat_rate"
                    + ", vat = @vat"
                    + ", tax_rate = @tax_rate"
                    + ", tax = @tax"
                    + ", post_pay_seq = @post_pay_seq"
                    + ", tax_pay_seq = @tax_pay_seq"
                    + ", tran_grp_seq = @tran_grp_seq"
                    + ", remarks = @remarks"
                    + ", tax_invoice_date = @tax_invoice_date"
                    + ", tax_invoice_id = @tax_invoice_id"

                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + ", from_bank_acc_id = @from_bank_acc_id"
                    + ", to_bank_acc_id = @to_bank_acc_id"
                    + " WHERE pay_seq = @pay_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(M_FIM_Payments info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@pay_seq", info.pay_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_payments"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE pay_seq = @pay_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                strSQL = "UPDATE fim_payment_cheques"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE pay_seq = @pay_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void ChangeStatus(M_FIM_Payments info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@pay_seq", info.pay_seq);
                dbManager.AddParameters(1, "@record_status", info.record_status);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_payments"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE pay_seq = @pay_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public bool UpdatePayinpack(IList<M_FIM_Payments> lst)
        {
            bool ret = false;
            int result = 0;
            string strSQL;
            try
            {
                ret = true;
                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.BeginTransaction();
                foreach (M_FIM_Payments info in lst)
                {
                    dbManager.AddParameters(0, "@pay_seq", info.pay_seq);
                    dbManager.AddParameters(1, "@real_pay_date", info.real_pay_date);
                    dbManager.AddParameters(2, "@post_pay_seq", info.post_pay_seq);
                    dbManager.AddParameters(3, "@updated_by", info.updated_by);
                    dbManager.AddParameters(4, "@updated_when", info.updated_when);

                    strSQL = "UPDATE fim_payments"
                        + " SET real_pay_date = @real_pay_date"
                        + ", post_pay_seq = @post_pay_seq"
                        + ", updated_by = @updated_by"
                        + ", updated_when = @updated_when"
                        + " WHERE pay_seq = @pay_seq";

                    result = dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                    if (result > 0)
                        ret &= true;
                    else
                        ret &= false;
                }
                if (ret)
                    dbManager.CommitTransaction();
                else
                    dbManager.RollBackTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return ret;
        }

        public void UpdateReceipt(M_FIM_Payments info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@pay_seq", info.pay_seq);
                dbManager.AddParameters(1, "@real_pay_date", info.real_pay_date);
                dbManager.AddParameters(2, "@receipt_no", info.receipt_no);
                dbManager.AddParameters(3, "@receipt_name", info.receipt_name);
                dbManager.AddParameters(4, "@updated_by", info.updated_by);
                dbManager.AddParameters(5, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_payments"
                    + " SET real_pay_date = @real_pay_date"
                    + ", receipt_no = @receipt_no"
                    + ", receipt_name = @receipt_name"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE pay_seq = @pay_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateTaxPaySeq(int pay_seq, int? tax_pay_seq, int update_by)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@pay_seq", pay_seq);
                dbManager.AddParameters(1, "@tax_pay_seq", tax_pay_seq);
                dbManager.AddParameters(2, "@updated_by", update_by);
                dbManager.AddParameters(3, "@updated_when", DateTime.Now);

                strSQL = "UPDATE fim_payments"
                    + " SET tax_pay_seq = @tax_pay_seq"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE pay_seq = @pay_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable Report_SumUpPayment(Nullable<DateTime> date)
        {
            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@date", date);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT p.ref_no, p.pay_name"
                    + ", p.pay_date"
                    + ", case when p.contact_seq is not null then c.NameTh"
                            + " else concat(np.prefix_short, emp.first_name, ' ', emp.last_name)"
                        + " end as payee_name"
                    + ", p.amount, che.cheque_no"
                    + " FROM fim_payments p"
                    + " LEFT JOIN fim_payment_cheques pc ON p.pay_seq = pc.pay_seq"
                    + " LEFT JOIN fim_cheques che ON pc.cheque_seq = che.cheque_seq"
                    + " LEFT JOIN IcmMasContact c ON p.contact_seq = c.ConId"
                    //+ " LEFT JOIN fim_mas_contact_persons cp ON p.contact_person_seq = cp.contact_person_seq"
                    + " LEFT JOIN hrm_ps_employees emp ON p.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq"
                    + " WHERE p.pay_date = @date"
                    + " AND p.record_status not like @record_status";


                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable Report_SumUpPaymentFT(DateTime? dateFrom, DateTime? dateTo, string bgm_source_seq)
        {
            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@from_date", dateFrom);
                dbManager.AddParameters(1, "@to_date", dateTo);
                dbManager.AddParameters(2, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(3, "@bgm_source_seq", bgm_source_seq);

                strSQL = "select *"
                    //+ " from (SELECT case when p.method_seq = 2 then che.pay_date"
                    //        + " else p.real_pay_date"
                    //        + " end as pay_date"
                    + " from (SELECT p.pay_date"
                        + ", p.ref_no"
                        + ", case when p.contact_seq is not null then c.NameTh"
                                + " else concat(np.prefix_short, emp.first_name, ' ', emp.last_name)"
                            + " end as payee_name"

                        + ", che.cheque_no, p.pay_name, p.amount"
                        + " FROM fim_payments p"
                        + " LEFT JOIN fim_payment_cheques pc ON p.pay_seq = pc.pay_seq"
                        + " LEFT JOIN fim_cheques che ON pc.cheque_seq = che.cheque_seq"
                        + " LEFT JOIN IcmMasContact c ON p.contact_seq = c.ConId"
                    //+ " LEFT JOIN fim_mas_contact_persons cp ON p.contact_person_seq = cp.contact_person_seq"
                        + " LEFT JOIN hrm_ps_employees emp ON p.emp_seq = emp.emp_seq"
                        + " LEFT JOIN mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq"
                        + " WHERE p.record_status not like @record_status"
                        + (bgm_source_seq != "" ? " and p.bgm_source_seq like @bgm_source_seq" : "")
                    + ") p"
                    + " where 0 = 0"
                    + (dateFrom != null ? " and p.pay_date >= @from_date" : "")
                    + (dateTo != null ? " and p.pay_date <= @to_date" : "")
                    + " order by p.pay_date, p.ref_no";


                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        #region Vat
        public double GetVatBuySale(int month, int year, int? bgm_source_seq, int? pay_seq)
        {
            string strSQL;
            DataTable dtb = null;
            DateTime dtFrom = new DateTime(year - 543, month, 1);
            DateTime dtTo = dtFrom.AddMonths(1).AddDays(-1);
            double dVatBuy = 0;
            double dVatSale = 0;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@from_date", dtFrom);//.AddMonths(-6));
                dbManager.AddParameters(1, "@to_date", dtTo);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(3, "@pay_seq", pay_seq);

                strSQL = "select a.vat"
                    + " from fim_payments a"
                    + " left join fim_vat_dtls vd on a.pay_seq = vd.pay_seq"
                    + " left join fim_vat v on vd.vat_seq = v.vat_seq"
                        + " and v.record_status <> 'D'"
                    + " where a.record_status = 'A'"
                    + " and a.method_seq in (1, 3)"
                    + " and coalesce(a.vat, 0) > 0"
                    + (bgm_source_seq != null ? " and a.bgm_source_seq = @bgm_source_seq" : "")
                    + " and a.real_pay_date is not null"
                    + " and a.real_pay_date >= @from_date"
                    + " and a.real_pay_date <= @to_date"
                    + " and (vd.vat_seq is null" + (pay_seq.HasValue ? " or v.pay_seq = @pay_seq)" : ")")

                    + " union all"

                    + " select a.vat"
                    + " from fim_payments a"
                    + " inner join (select pc.pay_seq, min(che.pay_date) as pay_date"
                        + " from fim_payment_cheques pc"
                        + " inner join fim_cheques che on pc.cheque_seq = che.cheque_seq"
                        + " where pc.record_status not like 'D'"
                        + " and che.record_status not in ('D', 'C')"
                        + " and che.pay_date is not null"
                        + " group by pc.pay_seq"
                        + ") che on a.pay_seq = che.pay_seq"
                    + " left join fim_vat_dtls vd on a.pay_seq = vd.pay_seq"
                    + " left join fim_vat v on vd.vat_seq = v.vat_seq"
                        + " and v.record_status <> 'D'"
                    + " where a.record_status = 'A'"
                    + " and a.method_seq = 2"
                    + " and coalesce(a.vat, 0) > 0"
                    + (bgm_source_seq != null ? " and a.bgm_source_seq = @bgm_source_seq" : "")
                    + " and che.pay_date >= @from_date"
                    + " and che.pay_date <= @to_date"
                    + " and (vd.vat_seq is null" + (pay_seq.HasValue ? " or v.pay_seq = @pay_seq)" : ")")

                    + " union all"

                    + " select a.vat"
                    + " from fim_payments a"
                    + " inner join fim_payments_payinpack pip on a.pay_seq = pip.pay_seq"
                    + " left join fim_vat_dtls vd on a.pay_seq = vd.pay_seq"
                    + " left join fim_vat v on vd.vat_seq = v.vat_seq"
                        + " and v.record_status <> 'D'"
                    + " where a.record_status = 'A'"
                    + " and pip.record_status <> 'D'"
                    + " and a.method_seq = 4"
                    + " and coalesce(a.vat, 0) > 0"
                    + (bgm_source_seq != null ? " and a.bgm_source_seq = @bgm_source_seq" : "")
                    + " and pip.pip_date >= @from_date"
                    + " and pip.pip_date <= @to_date"
                    + " and (vd.vat_seq is null" + (pay_seq.HasValue ? " or v.pay_seq = @pay_seq)" : ")")
                    
                    + " union all"

                    + " select ret.vat_amount"
                    + " from fim_money_return_dtls ret"
                    + " left join fim_money_returns re on ret.return_seq = re.return_seq"
                    + " left join fim_money_requests mr on re.money_req_seq = mr.money_req_seq"
                    + " left join fim_vat_dtls vd on ret.return_dtls_seq = vd.return_dtls_seq"
                    + " left join fim_vat v on vd.vat_seq = v.vat_seq"
                        + " and v.record_status <> 'D'"
                    + " where re.record_status = 'A'"
                    + " and coalesce(ret.vat_amount, 0) > 0"
                    + (bgm_source_seq != null ? " and a.bgm_source_seq = @bgm_source_seq" : "")
                    + " and re.return_date >= @from_date"
                    + " and re.return_date <= @to_date"
                    + " and (vd.vat_seq is null" + (pay_seq.HasValue ? " or v.pay_seq = @pay_seq)" : ")")
                    ;

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                if (dtb.Rows.Count > 0)
                {
                    for (int i = 0; i < dtb.Rows.Count; i++)
                    {
                        dVatBuy += cn.ConvertNullDouble(dtb.Rows[i][0]) ?? 0;
                    }
                }

                strSQL = "select r.vat_amount"
                    + " from fim_receives r"
                    + " left join fim_vat_dtls vd on r.receive_seq = vd.receive_seq"
                    + " left join fim_vat v on vd.vat_seq = v.vat_seq"
                        + " and v.record_status <> 'D'"
                    //+ " where receipt_no is not null"
                    //+ " and receipt_no <> ''"
                    + " where coalesce(r.vat_amount, 0) > 0"
                    + " and r.record_status not in ('D', 'C')"
                    + (bgm_source_seq != null ? " and r.bgm_source_seq = @bgm_source_seq" : "")
                    + " and r.receive_date >= @from_date"
                    + " and r.receive_date <= @to_date"
                    + " and (vd.vat_seq is null" + (pay_seq.HasValue ? " or v.pay_seq = @pay_seq)" : ")");

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                if (dtb.Rows.Count > 0)
                {
                    for (int i = 0; i < dtb.Rows.Count; i++)
                    {
                        dVatSale += cn.ConvertNullDouble(dtb.Rows[i][0]) ?? 0;
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dVatSale - dVatBuy;
        }

        public DataTable GetVatBuy(int month, int year, int? bgm_source_seq, int? pay_seq)
        {
            string strSQL;
            DataTable dtb = null;
            DateTime dtFrom = new DateTime(year - 543, month, 1);
            DateTime dtTo = dtFrom.AddMonths(1).AddDays(-1);

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@from_date", dtFrom);//.AddMonths(-6));
                dbManager.AddParameters(1, "@to_date", dtTo);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(3, "@pay_seq", pay_seq);

                strSQL = @"
select sum(coalesce(amount_vat, 0)) as amount_vat, sum(coalesce(amount, 0)) as amount 
from (select sum(coalesce(a.vat, 0)) as amount_vat, sum(coalesce(a.amount, 0)) as amount 
	from (select p.pay_seq
		, case when p.method_seq in (1,3) then p.real_pay_date
			else case when p.method_seq = 2 then che.pay_date
				else case when p.method_seq = 4 then pip.pip_date
					else null
					end
				end
			end as real_pay_date
		, sum(coalesce(mrd.amount_vat, 0)) as vat, sum(coalesce(mrd.amount, 0)) as amount 
		from fim_payments p 
		left join fim_money_requests mr on p.money_req_seq = mr.money_req_seq 
		left join fim_money_request_dtls mrd on mr.money_req_seq = mrd.money_req_seq 
		left join (select pc.pay_seq, min(che.pay_date) as pay_date 
			from fim_payment_cheques pc 
			inner join fim_cheques che on pc.cheque_seq = che.cheque_seq 
			where pc.record_status not like 'D' 
			and che.record_status not in ('D', 'C') 
			and che.pay_date is not null 
			group by pc.pay_seq
		) che on p.pay_seq = che.pay_seq
		left join fim_payments_payinpack pip on p.pay_seq = pip.pay_seq
			and pip.record_status <> 'D'
		where p.record_status = 'A' 
        and p.event_seq <> 27
		and mr.record_status = 'A' 
		and coalesce(mrd.amount_vat, 0) > 0"
                    + (bgm_source_seq != null ? " and p.bgm_source_seq = @bgm_source_seq" : "")
                    + @"
		group by p.pay_seq, p.method_seq, p.real_pay_date, che.pay_date, pip.pip_date
	) a
	left join fim_vat_dtls vd on a.pay_seq = vd.pay_seq 
	left join fim_vat v on vd.vat_seq = v.vat_seq 
		and v.record_status <> 'D'"
    + " where (vd.vat_seq is null" + (pay_seq.HasValue ? " or v.pay_seq = @pay_seq)" : ")")
	+ @"and a.real_pay_date is not null 
	and a.real_pay_date >= @from_date 
	and a.real_pay_date <= @to_date

	union all

	select sum(coalesce(a.vat, 0)) as amount_vat, sum(coalesce(a.amount, 0)) as amount 
	from (select ret.return_dtls_seq, sum(coalesce(ret.vat_amount, 0)) as vat, sum(coalesce(ret.amount, 0)) as amount 
		from fim_money_return_dtls ret 
		left join fim_money_returns re on ret.return_seq = re.return_seq 
		left join fim_money_requests mr on re.money_req_seq = mr.money_req_seq 
		where re.record_status = 'A' 
		and mr.record_status = 'A' 
		and coalesce(ret.vat_amount, 0) > 0"
                    + (bgm_source_seq != null ? " and mr.bgm_source_seq = @bgm_source_seq" : "")
                    + @"
		and re.return_date >= @from_date 
		and re.return_date <= @to_date 
		group by ret.return_dtls_seq
		) a 
	left join fim_vat_dtls vd on a.return_dtls_seq = vd.return_dtls_seq 
	left join fim_vat v on vd.vat_seq = v.vat_seq 
		and v.record_status <> 'D'"
    + " where (vd.vat_seq is null" + (pay_seq.HasValue ? " or v.pay_seq = @pay_seq)" : ")")
+ ") t";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                return dtb;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetVatSale(int month, int year, int? bgm_source_seq, int? pay_seq)
        {
            string strSQL;
            DataTable dtb = null;
            DateTime dtFrom = new DateTime(year - 543, month, 1);
            DateTime dtTo                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    = dtFrom.AddMonths(1).AddDays(-1);

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@from_date", dtFrom);//.AddMonths(-6));
                dbManager.AddParameters(1, "@to_date", dtTo);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(3, "@pay_seq", pay_seq);

                strSQL = "select sum(coalesce(tt.amount_vat, 0)) as amount_vat, sum(coalesce(tt.amount, 0)) as amount, sum(coalesce(tt.non_vat_sale_amount, 0)) as non_vat_sale_amount"
                    + " from (select sum(coalesce(r.vat_amount, 0)) as amount_vat, sum(coalesce(r.amount, 0)) as amount"
                    + ", (select sum(coalesce(r2.amount, 0)) as amount"
                        + " from fim_receives r2"
                        + " left join fim_mas_receive_types rt on r2.receive_type_seq = rt.receive_type_seq"
                        + " where coalesce(r2.vat_amount, 0) = 0"
                        + " and r2.receive_mode = 2"
                        + " and rt.temp_income <> 1"
                        + " and r2.record_status not in ('D', 'C')"
                        + (bgm_source_seq != null ? " and r2.bgm_source_seq = @bgm_source_seq" : "")
                        + " and r2.receive_date >= @from_date"
                        + " and r2.receive_date <= @to_date"
                        + ") as non_vat_sale_amount"
                    + " from fim_receives r"
                    + " left join fim_vat_dtls vd on r.receive_seq = vd.receive_seq"
                    + " left join fim_vat v on vd.vat_seq = v.vat_seq"
                        + " and v.record_status <> 'D'"
                    + " where r.record_status not in ('D', 'C')"
                    + (bgm_source_seq != null ? " and r.bgm_source_seq = @bgm_source_seq" : "")
                    + " and r.receive_date >= @from_date"
                    + " and r.receive_date <= @to_date"
                    + " and coalesce(r.vat_amount, 0) > 0"
                    + " and (vd.vat_seq is null" + (pay_seq.HasValue ? " or v.pay_seq = @pay_seq)" : ")")
                    
                    + @" union all

select - sum(coalesce(t.vat, 0)) as vat_amount, - sum(coalesce(t.amount, 0)) as amount
, 0 as non_vat_sale_amount
from (select 
case when p.method_seq = 2 then che.pay_date
    else case when p.method_seq = 4 then pip.pip_date
        else p.real_pay_date
    end
end as pay_date
, p.vat, p.amount, 0 as non_vat_sale_amount
from fim_payments p
left join (select pc.pay_seq, min(che.pay_date) as pay_date
    from fim_payment_cheques pc
    inner join fim_cheques che on pc.cheque_seq = che.cheque_seq
    where pc.record_status not like 'D'
    and che.record_status not in ('D', 'C')
    and che.pay_date is not null
    group by pc.pay_seq
    ) che on p.pay_seq = che.pay_seq
left join fim_payments_payinpack pip on p.pay_seq = pip.pay_seq
left join IcmMasContact c on p.contact_seq = c.ConId
left join hrm_ps_employees emp on p.emp_seq = emp.emp_seq
left join mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq
--left join fim_receives r on p.pay_seq = r.pay_seq
left join fim_vat_dtls vd on p.pay_seq = vd.pay_seq 
left join fim_vat v on vd.vat_seq = v.vat_seq and v.record_status <> 'D' 

where p.record_status = 'A'
and p.event_seq = 27
and p.vat > 0"
                    + " and (vd.vat_seq is null" + (pay_seq.HasValue ? " or v.pay_seq = @pay_seq)" : ")")
+ @") t
where t.pay_date >= @from_date
and t.pay_date <= @to_date
)tt"
                    ;

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                return dtb;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public List<M_FIM_Payments> GetIncludeVatPayment(int month, int year, int? bgm_source_seq, int? vat_seq)
        {
            string strSQL;
            DataTable dtb = null;
            DateTime dtFrom = new DateTime(year - 543, month, 1);
            DateTime dtTo = dtFrom.AddMonths(1).AddDays(-1);

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@from_date", dtFrom);//.AddMonths(-6));
                dbManager.AddParameters(1, "@to_date", dtTo);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(3, "@vat_seq", vat_seq);

                strSQL = "select a.*"
                    + " from fim_payments a"
                    + " left join fim_vat_dtls vd on a.pay_seq = vd.pay_seq"
                    + " left join fim_vat v on vd.vat_seq = v.vat_seq"
                        + " and v.record_status <> 'D'"
                    + " where a.record_status = 'A'"
                    + " and coalesce(a.vat, 0) > 0"
                    + (bgm_source_seq != null ? " and a.bgm_source_seq = @bgm_source_seq" : "")
                    + " and a.method_seq in (1,3)"
                    + " and a.real_pay_date is not null"
                    + " and a.real_pay_date >= @from_date"
                    + " and a.real_pay_date <= @to_date"
                    + " and (vd.vat_seq is null" + (vat_seq.HasValue ? " or v.vat_seq = @vat_seq)" : ")")
                    
                    + " union all"
                    
                    + " select a.*"
                    + " from fim_payments a"
                    + " inner join (select pc.pay_seq, min(che.pay_date) as pay_date"
                            + " from fim_payment_cheques pc"
                            + " inner join fim_cheques che on pc.cheque_seq = che.cheque_seq"
                            + " where pc.record_status not like 'D'"
                            + " and che.record_status not in ('D', 'C')"
                            + " and che.pay_date is not null"
                            + " group by pc.pay_seq"
                            + ") che on a.pay_seq = che.pay_seq"
                    + " left join fim_vat_dtls vd on a.pay_seq = vd.pay_seq"
                    + " left join fim_vat v on vd.vat_seq = v.vat_seq"
                        + " and v.record_status <> 'D'"
                    + " where a.record_status = 'A'"
                    + " and coalesce(a.vat, 0) > 0"
                    + (bgm_source_seq != null ? " and a.bgm_source_seq = @bgm_source_seq" : "")
                    + " and a.method_seq = 2"
                    + " and che.pay_date >= @from_date"
                    + " and che.pay_date <= @to_date"
                    + " and (vd.vat_seq is null" + (vat_seq.HasValue ? " or v.vat_seq = @vat_seq)" : ")")
                    
                    + " union all"
                    
                    + " select a.*"
                    + " from fim_payments a"
                    + " inner join fim_payments_payinpack pip on a.pay_seq = pip.pay_seq"
                    + " left join fim_vat_dtls vd on a.pay_seq = vd.pay_seq"
                    + " left join fim_vat v on vd.vat_seq = v.vat_seq"
                        + " and v.record_status <> 'D'"
                    + " where a.record_status = 'A'"
                    + " and pip.record_status <> 'D'"
                    + " and coalesce(a.vat, 0) > 0"
                    + (bgm_source_seq != null ? " and a.bgm_source_seq = @bgm_source_seq" : "")
                    + " and a.method_seq = 4"
                    + " and pip.pip_date >= @from_date"
                    + " and pip.pip_date <= @to_date"
                    + " and (vd.vat_seq is null" + (vat_seq.HasValue ? " or v.vat_seq = @vat_seq)" : ")")
                    ;

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                var models = new List<M_FIM_Payments>();
                foreach(DataRow dr in dtb.Rows)
                {
                    var model = new M_FIM_Payments()
                    {
                        pay_seq = Convert.ToInt32(dr["pay_seq"].ToString()),
                        bgm_source_seq = Convert.ToInt32(dr["bgm_source_seq"].ToString()),
                        work_area_seq = Convert.ToInt32(dr["work_area_seq"].ToString()),
                        bgm_year = Convert.ToInt32(dr["bgm_year"].ToString()),
                        event_seq = cn.ConvertNullInt(dr["event_seq"].ToString()),
                        pay_date = cn.ConvertNullDateTime(dr["pay_date"].ToString()),
                        real_pay_date = cn.ConvertNullDateTime(dr["real_pay_date"].ToString()),
                        ref_no = dr["ref_no"].ToString(),
                        receipt_no = dr["receipt_no"].ToString(),
                        receipt_name = dr["receipt_name"].ToString(),
                        pay_name = dr["pay_name"].ToString(),
                        method_seq = cn.ConvertNullInt(dr["method_seq"].ToString()),
                        money_req_seq = cn.ConvertNullInt(dr["money_req_seq"].ToString()),
                        emp_seq = cn.ConvertNullInt(dr["emp_seq"].ToString()),
                        contact_seq = cn.ConvertNullInt(dr["contact_seq"].ToString()),
                        bidder_seq = cn.ConvertNullInt(dr["bidder_seq"].ToString()),
                        po_seq = cn.ConvertNullInt(dr["po_seq"].ToString()),
                        amount = cn.ConvertNullDouble(dr["amount"].ToString()),
                        vat_rate = cn.ConvertNullDouble(dr["vat_rate"].ToString()),
                        vat = cn.ConvertNullDouble(dr["vat"].ToString()),
                        tax_rate = cn.ConvertNullDouble(dr["tax_rate"].ToString()),
                        tax = cn.ConvertNullDouble(dr["tax"].ToString()),
                        post_pay_seq = cn.ConvertNullInt(dr["post_pay_seq"].ToString()),
                        tax_pay_seq = cn.ConvertNullInt(dr["tax_pay_seq"].ToString()),
                        tran_grp_seq = cn.ConvertNullInt(dr["tran_grp_seq"].ToString()),
                        remarks = dr["remarks"].ToString(),
                        record_status = dr["record_status"].ToString(),
                        to_bank_acc_id = cn.ConvertNullInt(dr["to_bank_acc_id"].ToString()),
                        from_bank_acc_id = cn.ConvertNullInt(dr["from_bank_acc_id"].ToString()),
                        penalty_amount = cn.ConvertNullDouble(dr["penalty_amount"].ToString()),
                        redemption_amount = cn.ConvertNullDouble(dr["redemption_amount"].ToString()),
                    };

                    models.Add(model);
                }

                return models;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public List<M_FIM_MoneyReturnDtls> GetIncludeVatReturnDtls(int month, int year, int? bgm_source_seq, int? vat_seq)
        {
            string strSQL;
            DataTable dtb = null;
            DateTime dtFrom = new DateTime(year - 543, month, 1);
            DateTime dtTo = dtFrom.AddMonths(1).AddDays(-1);

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@from_date", dtFrom);//.AddMonths(-6));
                dbManager.AddParameters(1, "@to_date", dtTo);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(3, "@vat_seq", vat_seq);

                strSQL = "select red.*"
                    + " from fim_money_return_dtls red"
                    + " left join fim_money_returns re on red.return_seq = re.return_seq"
                    + " left join fim_money_requests mr on re.money_req_seq = mr.money_req_seq"
                    + " left join fim_vat_dtls vd on red.return_dtls_seq = vd.return_dtls_seq"
                    + " left join fim_vat v on vd.vat_seq = v.vat_seq"
                        + " and v.record_status <> 'D'"
                    + " where re.record_status = 'A'"
                    + " and coalesce(red.vat_amount, 0) > 0"
                    + (bgm_source_seq != null ? " and mr.bgm_source_seq = @bgm_source_seq" : "")
                    + " and re.return_date >= @from_date"
                    + " and re.return_date <= @to_date"
                    + " and (vd.vat_seq is null" + (vat_seq.HasValue ? " or v.vat_seq = @vat_seq)" : ")");

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                var models = new List<M_FIM_MoneyReturnDtls>();
                foreach (DataRow dr in dtb.Rows)
                {
                    var model = new M_FIM_MoneyReturnDtls()
                    {
                        return_dtls_seq = Convert.ToInt32(dr["return_dtls_seq"].ToString()),
                        return_seq = cn.ConvertNullInt(dr["return_seq"]),
                        money_req_dtls_seq = cn.ConvertNullInt(dr["money_req_dtls_seq"]),
                        amount = cn.ConvertNullDouble(dr["amount"]),
                        vat_amount = cn.ConvertNullDouble(dr["vat_amount"]),
                        record_status = dr["record_status"].ToString(),
                    };

                    models.Add(model);
                }

                return models;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public List<M_FIM_Receives> GetIncludeVatReceive(int month, int year, int? bgm_source_seq, int? vat_seq)
        {
            string strSQL;
            DataTable dtb = null;
            DateTime dtFrom = new DateTime(year - 543, month, 1);
            DateTime dtTo = dtFrom.AddMonths(1).AddDays(-1);

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@from_date", dtFrom);//.AddMonths(-6));
                dbManager.AddParameters(1, "@to_date", dtTo);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(3, "@vat_seq", vat_seq);

                strSQL = "select a.*"
                    + " from fim_receives a"
                    + " left join fim_mas_receive_types rt on a.receive_type_seq = rt.receive_type_seq"
                    + " left join fim_vat_dtls vd on a.receive_seq = vd.receive_seq"
                    + " left join fim_vat v on vd.vat_seq = v.vat_seq"
                        + " and v.record_status <> 'D'"
                    + " where a.record_status not in ('D', 'C')"
                    + " and a.receive_mode = 2"
                    + " and rt.temp_income = 0"
                    + " and rt.not_sale = 0"
                    //+ " and coalesce(a.vat_amount, 0) > 0"
                    + (bgm_source_seq != null ? " and a.bgm_source_seq = @bgm_source_seq" : "")
                    + " and a.receive_date >= @from_date"
                    + " and a.receive_date <= @to_date"
                    + " and (vd.vat_seq is null" + (vat_seq.HasValue ? " or v.vat_seq = @vat_seq)" : ")");

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                var models = new List<M_FIM_Receives>();
                foreach (DataRow dr in dtb.Rows)
                {
                    var model = new M_FIM_Receives()
                    {
                        receive_seq = Convert.ToInt32(dr["receive_seq"].ToString()),
                        bgm_source_seq = cn.ConvertNullInt(dr["bgm_source_seq"].ToString()),
                        work_area_seq = cn.ConvertNullInt(dr["work_area_seq"].ToString()),
                        bgm_year = cn.ConvertNullInt(dr["bgm_year"].ToString()),
                        receive_type_seq = cn.ConvertNullInt(dr["receive_type_seq"].ToString()),
                        receipt_no = dr["receipt_no"].ToString(),
                        receive_date = cn.ConvertNullDateTime(dr["receive_date"].ToString()),
                        manual_receipt = dr["manual_receipt"].ToString(),
                        receive_mode = dr["receive_mode"].ToString(),
                        method_seq = cn.ConvertNullInt(dr["method_seq"].ToString()),
                        cheque_no = dr["cheque_no"].ToString(),
                        branch_seq = cn.ConvertNullInt(dr["branch_seq"].ToString()),
                        bank_acc_seq = cn.ConvertNullInt(dr["bank_acc_seq"].ToString()),
                        contact_seq = cn.ConvertNullInt(dr["contact_seq"].ToString()),
                        emp_seq = cn.ConvertNullInt(dr["emp_seq"].ToString()),
                        temp_contact = dr["temp_contact"].ToString(),
                        amount = cn.ConvertNullDouble(dr["amount"].ToString()),
                        received_by = cn.ConvertNullInt(dr["received_by"].ToString()),
                        remarks = dr["remarks"].ToString(),
                        icm_bid_rec_seq = cn.ConvertNullInt(dr["icm_bid_rec_seq"].ToString()),
                        icm_bid_off_col_seq = cn.ConvertNullInt(dr["icm_bid_off_col_seq"].ToString()),
                        icm_po_con_col_seq = cn.ConvertNullInt(dr["icm_po_con_col_seq"].ToString()),
                        icm_po_pre_col_seq = cn.ConvertNullInt(dr["icm_po_pre_col_seq"].ToString()),
                        icm_dn_pen_seq = cn.ConvertNullInt(dr["icm_dn_pen_seq"].ToString()),
                        icm_po_instal_seq = cn.ConvertNullInt(dr["icm_po_instal_seq"].ToString()),
                        icm_stockmm_seq = cn.ConvertNullInt(dr["icm_stockmm_seq"].ToString()),
                        tran_grp_seq = cn.ConvertNullInt(dr["tran_grp_seq"].ToString()),
                        deposit_seq = cn.ConvertNullInt(dr["deposit_seq"].ToString()),
                        petition_seq = cn.ConvertNullInt(dr["petition_seq"].ToString()),
                        money_req_seq = cn.ConvertNullInt(dr["money_req_seq"].ToString()),
                        pay_seq = cn.ConvertNullInt(dr["pay_seq"].ToString()),
                        record_status = dr["record_status"].ToString(),
                        vat_amount = cn.ConvertNullDecimal(dr["vat_amount"].ToString()),
                        exclude_vat = cn.ConvertNullDecimal(dr["exclude_vat"].ToString()),
                        vat_percent = cn.ConvertNullDecimal(dr["vat_percent"].ToString()),
                        recive_remark = dr["recive_remark"].ToString(),
                        tax_percent = cn.ConvertNullDecimal(dr["tax_percent"].ToString()),
                        include_vat = cn.ConvertNullDecimal(dr["include_vat"].ToString()),
                        tax = cn.ConvertNullDecimal(dr["tax"].ToString()),
                        ref_no = dr["ref_no"].ToString(),
                        contact_address = dr["contact_address"].ToString(),
                        contact_tax_code = dr["contact_tax_code"].ToString(),
                        contact_hq = dr["contact_hq"].ToString(),
                        contact_branch = dr["contact_branch"].ToString(),
                        source = dr["source"].ToString(),
                        print_out = Convert.ToInt32(dr["print_out"].ToString()),
                    };

                    models.Add(model);
                }

                return models;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public double GetPrevVatBuySale(int month, int year)
        {
            string strSQL;
            if (month == 1)
            {
                month = 1;
                year--;
            }
            else
            {
                month--;
            }

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@month", month);
                dbManager.AddParameters(1, "@year", year);

                strSQL = "select v.next_month_vat_amount"
                    + " from fim_vat v"
                    + " where v.record_status <> 'D'"
                    + " and v.year_no = @year"
                    + " and v.month_no = @month";

                var dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                if (dtb.Rows.Count > 0)
                {
                    return cn.ConvertNullDouble(dtb.Rows[0][0]) ?? 0;
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public double GetPrevVatBuySaleOld(int month, int year)
        {
            string strSQL;
            DataTable dtb = null;
            DateTime dtFrom = new DateTime(year - 543, month, 1);
            double dVatBuy = 0;
            double dVatSale = 0;
            double dPayVat = 0;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@from_date", dtFrom);
                dbManager.AddParameters(1, "@year", year - 543);

                strSQL = "select a.vat"
                    + " from fim_payments a"
                    + " left join fim_payment_cheques b on a.pay_seq=b.pay_seq"
                        + " and b.record_status not like 'D'"
                    + " left join fim_cheques c on c.cheque_seq =b.cheque_seq"
                        + " and b.record_status not like 'D'"
                    + " where a.record_status = 'A'"
                    + " and coalesce(a.vat, 0) > 0"
                    + " and ((a.method_seq = 2 and c.pay_date < @from_date and year(c.pay_date) = @year)"
                        + " or (a.method_seq != 2 and a.real_pay_date < @from_date and year(a.real_pay_date) = @year))"

                    + " select a.vat"
                    + " from fim_payments a"
                    + " where a.record_status = 'A'"
                    + " and coalesce(a.vat, 0) > 0"
                    + " and ((a.method_seq = 2 and c.pay_date < @from_date and year(c.pay_date) = @year)"
                        + " or (a.method_seq != 2 and a.real_pay_date < @from_date and year(a.real_pay_date) = @year))"  
                    ;

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                if (dtb.Rows.Count > 0)
                {
                    for (int i = 0; i < dtb.Rows.Count; i++)
                    {
                        dVatBuy += cn.ConvertNullDouble(dtb.Rows[i][0]) ?? 0;
                    }
                }

                strSQL = "select vat_amount"
                    + " from fim_receives"
                    + " where 0 = 0"
                    //+ " and receipt_no is not null"
                    + " and r.record_status not in ('D', 'C')"
                    //+ " and receipt_no <> ''"
                    + " and coalesce(vat_amount, 0) > 0"
                    + " and receive_date < @from_date and year(receive_date) = @year";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                if (dtb.Rows.Count > 0)
                {
                    for (int i = 0; i < dtb.Rows.Count; i++)
                    {
                        dVatSale += cn.ConvertNullDouble(dtb.Rows[i][0]) ?? 0;
                    }
                }

                strSQL = "select a.amount"
                    + " from fim_payments a"
                    + " left join fim_payment_cheques b on a.pay_seq=b.pay_seq"
                        + " and b.record_status not like 'D'"
                    + " left join fim_cheques c on c.cheque_seq =b.cheque_seq"
                        + " and b.record_status not like 'D'"
                    + " where a.record_status = 'A'"
                    + " and a.event_seq = 26"
                    + " and ((a.method_seq = 2 and year(c.pay_date) = @year)"
                        + " or (a.method_seq != 2 and year(a.real_pay_date) = @year))";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                if (dtb.Rows.Count > 0)
                {
                    for (int i = 0; i < dtb.Rows.Count; i++)
                    {
                        dPayVat += cn.ConvertNullDouble(dtb.Rows[i][0]) ?? 0;
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dVatSale - dVatBuy - dPayVat;
        }

        public List<M_FIM_Vat> GetVatFull(string bgm_source_seq, string bgm_year)
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_year", bgm_year);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);

                strSQL = "select a.*, p.ref_no, rs.record_status_name"
                    + " from fim_vat a"
                    + " left join fim_payments p on a.pay_seq = p.pay_seq"
                        + " and pay.record_status not like @delete_status"
                    + " LEFT JOIN sys_record_status rs ON a.record_status = rs.record_status"
                    + " where a.record_status not like @delete_status"
                    + (string.IsNullOrEmpty(bgm_source_seq) ? " and a.bgm_source_seq = @bgm_source_seq" : "")
                    + (string.IsNullOrEmpty(bgm_year) ? " and a.bgm_year = @bgm_year" : "");

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                var models = new List<M_FIM_Vat>();
                foreach (DataRow dr in dtb.Rows)
                {
                    var model = new M_FIM_Vat()
                    {
                        vat_seq = Convert.ToInt32(dr["vat_seq"]),
                        bgm_source_seq = Convert.ToInt32(dr["bgm_source_seq"]),
                        bgm_year = Convert.ToInt32(dr["bgm_year"]),
                        year_no = Convert.ToInt32(dr["year_no"]),
                        month_no = Convert.ToInt32(dr["month_no"]),
                        sale_amount = cn.ConvertNullDouble(dr["sale_amount"].ToString()),
                        vat_sale_amount = cn.ConvertNullDouble(dr["vat_sale_amount"].ToString()),
                        non_vat_sale_amount = cn.ConvertNullDouble(dr["non_vat_sale_amount"].ToString()),
                        buy_amount = cn.ConvertNullDouble(dr["buy_amount"].ToString()),
                        vat_buy_amount = cn.ConvertNullDouble(dr["vat_buy_amount"].ToString()),
                        pay_seq = cn.ConvertNullInt(dr["pay_seq"].ToString()),
                        next_month_vat_amount = cn.ConvertNullDouble(dr["next_month_vat_amount"].ToString()),
                        record_status = dr["record_status"].ToString(),
                        pay_ref_no = dr["ref_no"].ToString(),
                        record_status_name = dr["record_status_name"].ToString(),

                        cal_vat_buy_amount = cn.ConvertNullDouble(dr["cal_vat_buy_amount"].ToString()),
                        cal_vat_sale_amount = cn.ConvertNullDouble(dr["cal_vat_sale_amount"].ToString()),
                        diff_vat_buy_amount = cn.ConvertNullDouble(dr["diff_vat_buy_amount"].ToString()),
                        diff_vat_sale_amount = cn.ConvertNullDouble(dr["diff_vat_sale_amount"].ToString()),
                    };
                    models.Add(model);
                }

                return models;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public M_FIM_Vat GetVatInfo(string vat_seq)
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@vat_seq", vat_seq);

                strSQL = "select a.*, p.ref_no, rs.record_status_name"
                    + " from fim_vat a"
                    + " left join fim_payments p on a.pay_seq = p.pay_seq"
                        + " and p.record_status not like 'D'"
                    + " LEFT JOIN sys_record_status rs ON a.record_status = rs.record_status"
                    + " where a.vat_seq = @vat_seq";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                if (dtb.Rows.Count > 0)
                {
                    var model = new M_FIM_Vat()
                    {
                        vat_seq = Convert.ToInt32(dtb.Rows[0]["vat_seq"]),
                        bgm_source_seq = Convert.ToInt32(dtb.Rows[0]["bgm_source_seq"]),
                        bgm_year = Convert.ToInt32(dtb.Rows[0]["bgm_year"]),
                        year_no = Convert.ToInt32(dtb.Rows[0]["year_no"]),
                        month_no = Convert.ToInt32(dtb.Rows[0]["month_no"]),
                        sale_amount = cn.ConvertNullDouble(dtb.Rows[0]["sale_amount"].ToString()),
                        vat_sale_amount = cn.ConvertNullDouble(dtb.Rows[0]["vat_sale_amount"].ToString()),
                        non_vat_sale_amount = cn.ConvertNullDouble(dtb.Rows[0]["non_vat_sale_amount"].ToString()),
                        buy_amount = cn.ConvertNullDouble(dtb.Rows[0]["buy_amount"].ToString()),
                        vat_buy_amount = cn.ConvertNullDouble(dtb.Rows[0]["vat_buy_amount"].ToString()),
                        pay_seq = cn.ConvertNullInt(dtb.Rows[0]["pay_seq"].ToString()),
                        next_month_vat_amount = cn.ConvertNullDouble(dtb.Rows[0]["next_month_vat_amount"].ToString()),
                        record_status = dtb.Rows[0]["record_status"].ToString(),
                        pay_ref_no = dtb.Rows[0]["ref_no"].ToString(),
                        record_status_name = dtb.Rows[0]["record_status_name"].ToString(),

                        cal_vat_buy_amount = cn.ConvertNullDouble(dtb.Rows[0]["cal_vat_buy_amount"].ToString()),
                        cal_vat_sale_amount = cn.ConvertNullDouble(dtb.Rows[0]["cal_vat_sale_amount"].ToString()),
                        diff_vat_buy_amount = cn.ConvertNullDouble(dtb.Rows[0]["diff_vat_buy_amount"].ToString()),
                        diff_vat_sale_amount = cn.ConvertNullDouble(dtb.Rows[0]["diff_vat_sale_amount"].ToString()),
                    };

                    return model;
                }

                return null;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public M_FIM_Vat GetVatInfoFromPaySeq(string pay_seq)
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@pay_seq", pay_seq);

                strSQL = "select a.*, p.ref_no, rs.record_status_name"
                    + " from fim_vat a"
                    + " left join fim_payments p on a.pay_seq = p.pay_seq"
                        + " and p.record_status not like 'D'"
                    + " LEFT JOIN sys_record_status rs ON a.record_status = rs.record_status"
                    + " where a.pay_seq = @pay_seq";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                if (dtb.Rows.Count > 0)
                {
                    var model = new M_FIM_Vat()
                    {
                        vat_seq = Convert.ToInt32(dtb.Rows[0]["vat_seq"]),
                        bgm_source_seq = Convert.ToInt32(dtb.Rows[0]["bgm_source_seq"]),
                        bgm_year = Convert.ToInt32(dtb.Rows[0]["bgm_year"]),
                        year_no = Convert.ToInt32(dtb.Rows[0]["year_no"]),
                        month_no = Convert.ToInt32(dtb.Rows[0]["month_no"]),
                        sale_amount = cn.ConvertNullDouble(dtb.Rows[0]["sale_amount"].ToString()),
                        vat_sale_amount = cn.ConvertNullDouble(dtb.Rows[0]["vat_sale_amount"].ToString()),
                        non_vat_sale_amount = cn.ConvertNullDouble(dtb.Rows[0]["non_vat_sale_amount"].ToString()),
                        buy_amount = cn.ConvertNullDouble(dtb.Rows[0]["buy_amount"].ToString()),
                        vat_buy_amount = cn.ConvertNullDouble(dtb.Rows[0]["vat_buy_amount"].ToString()),
                        pay_seq = cn.ConvertNullInt(dtb.Rows[0]["pay_seq"].ToString()),
                        next_month_vat_amount = cn.ConvertNullDouble(dtb.Rows[0]["next_month_vat_amount"].ToString()),
                        record_status = dtb.Rows[0]["record_status"].ToString(),
                        pay_ref_no = dtb.Rows[0]["ref_no"].ToString(),
                        record_status_name = dtb.Rows[0]["record_status_name"].ToString(),
                        cal_vat_buy_amount = cn.ConvertNullDouble(dtb.Rows[0]["cal_vat_buy_amount"].ToString()),
                        cal_vat_sale_amount = cn.ConvertNullDouble(dtb.Rows[0]["cal_vat_sale_amount"].ToString()),
                        diff_vat_buy_amount = cn.ConvertNullDouble(dtb.Rows[0]["diff_vat_buy_amount"].ToString()),
                        diff_vat_sale_amount = cn.ConvertNullDouble(dtb.Rows[0]["diff_vat_sale_amount"].ToString()),
                    };

                    return model;
                }

                return null;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string InsertVat(M_FIM_Vat info)
        {
            string strSQL;
            string strSeq = "";
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(19);

                dbManager.AddParameters(0, "@vat_seq", info.vat_seq);
                dbManager.AddParameters(1, "@bgm_source_seq", info.bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_year", info.bgm_year);
                dbManager.AddParameters(3, "@year_no", info.year_no);
                dbManager.AddParameters(4, "@month_no", info.month_no);
                dbManager.AddParameters(5, "@sale_amount", info.sale_amount);
                dbManager.AddParameters(6, "@vat_sale_amount", info.vat_sale_amount);
                dbManager.AddParameters(7, "@non_vat_sale_amount", info.non_vat_sale_amount);
                dbManager.AddParameters(8, "@buy_amount", info.buy_amount);
                dbManager.AddParameters(9, "@vat_buy_amount", info.vat_buy_amount);
                dbManager.AddParameters(10, "@pay_seq", info.pay_seq);
                dbManager.AddParameters(11, "@next_month_vat_amount", info.next_month_vat_amount);
                dbManager.AddParameters(12, "@record_status", info.record_status);

                dbManager.AddParameters(13, "@created_by", info.created_by);
                dbManager.AddParameters(14, "@created_when", info.created_when);

                dbManager.AddParameters(15, "@cal_vat_buy_amount", info.cal_vat_buy_amount);
                dbManager.AddParameters(16, "@cal_vat_sale_amount", info.cal_vat_sale_amount);
                dbManager.AddParameters(17, "@diff_vat_buy_amount", info.diff_vat_buy_amount);
                dbManager.AddParameters(18, "@diff_vat_sale_amount", info.diff_vat_sale_amount);

                strSQL = "INSERT INTO fim_vat"
                    + "(bgm_source_seq,bgm_year,year_no,month_no,sale_amount,vat_sale_amount"
                    + ",non_vat_sale_amount,buy_amount,vat_buy_amount,pay_seq,next_month_vat_amount"
                    + ", cal_vat_buy_amount, cal_vat_sale_amount, diff_vat_buy_amount, diff_vat_sale_amount"
                    + ",record_status,created_by,created_when)"
                    + " VALUES(@bgm_source_seq, @bgm_year, @year_no, @month_no, @sale_amount, @vat_sale_amount"
                    + ", @non_vat_sale_amount, @buy_amount, @vat_buy_amount, @pay_seq, @next_month_vat_amount"
                    + ", @cal_vat_buy_amount, @cal_vat_sale_amount, @diff_vat_buy_amount, @diff_vat_sale_amount"
                    + ", @record_status, @created_by, @created_when);"
                    + " select scope_identity()";
                strSeq = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }

            return strSeq;
        }

        public void UpdateVat(M_FIM_Vat info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(19);

                dbManager.AddParameters(0, "@vat_seq", info.vat_seq);
                dbManager.AddParameters(1, "@bgm_source_seq", info.bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_year", info.bgm_year);
                dbManager.AddParameters(3, "@year_no", info.year_no);
                dbManager.AddParameters(4, "@month_no", info.month_no);
                dbManager.AddParameters(5, "@sale_amount", info.sale_amount);
                dbManager.AddParameters(6, "@vat_sale_amount", info.vat_sale_amount);
                dbManager.AddParameters(7, "@non_vat_sale_amount", info.non_vat_sale_amount);
                dbManager.AddParameters(8, "@buy_amount", info.buy_amount);
                dbManager.AddParameters(9, "@vat_buy_amount", info.vat_buy_amount);
                dbManager.AddParameters(10, "@pay_seq", info.pay_seq);
                dbManager.AddParameters(11, "@next_month_vat_amount", info.next_month_vat_amount);
                dbManager.AddParameters(12, "@record_status", info.record_status);

                dbManager.AddParameters(13, "@updated_by", info.created_by);
                dbManager.AddParameters(14, "@updated_when", info.created_when);

                dbManager.AddParameters(15, "@cal_vat_buy_amount", info.cal_vat_buy_amount);
                dbManager.AddParameters(16, "@cal_vat_sale_amount", info.cal_vat_sale_amount);
                dbManager.AddParameters(17, "@diff_vat_buy_amount", info.diff_vat_buy_amount);
                dbManager.AddParameters(18, "@diff_vat_sale_amount", info.diff_vat_sale_amount);

                strSQL = @"update fim_vat
set bgm_source_seq = @bgm_source_seq
,bgm_year = @bgm_year
,year_no = @year_no
,month_no = @month_no
,sale_amount = @sale_amount
,vat_sale_amount = @vat_sale_amount
,non_vat_sale_amount = @non_vat_sale_amount
,buy_amount = @buy_amount
,vat_buy_amount = @vat_buy_amount
,pay_seq = @pay_seq
,next_month_vat_amount = @next_month_vat_amount
,cal_vat_buy_amount = @cal_vat_buy_amount
,cal_vat_sale_amount = @cal_vat_sale_amount
,diff_vat_buy_amount = @diff_vat_buy_amount
,diff_vat_sale_amount = @diff_vat_sale_amount
,record_status = @record_status
,updated_by = @updated_by
,updated_when = @updated_when
where vat_seq = @vat_seq";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteVat(string vat_seq, int emp_seq)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@vat_seq", vat_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);

                dbManager.AddParameters(2, "@updated_by", emp_seq);
                dbManager.AddParameters(3, "@updated_when", DateTime.Now);

                strSQL = @"update fim_vat
set record_status = @record_status
, updated_by = @updated_by
, updated_when = @updated_when
where vat_seq = @vat_seq";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string InsertVatDtls(M_FIM_Vat_Dtls info)
        {
            string strSQL;
            string strSeq = "";
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@vat_dtls_seq", info.vat_dtls_seq);
                dbManager.AddParameters(1, "@vat_seq", info.vat_seq);
                dbManager.AddParameters(2, "@pay_seq", info.pay_seq);
                dbManager.AddParameters(3, "@vat_buy_amount", info.vat_buy_amount);
                dbManager.AddParameters(4, "@receive_seq", info.receive_seq);
                dbManager.AddParameters(5, "@vat_sale_amount", info.vat_sale_amount);
                dbManager.AddParameters(6, "@return_dtls_seq", info.return_dtls_seq);

                strSQL = "INSERT INTO fim_vat_dtls"
                    + "(vat_seq, pay_seq, vat_buy_amount, receive_seq, vat_sale_amount, return_dtls_seq)"
                    + " VALUES(@vat_seq, @pay_seq, @vat_buy_amount, @receive_seq, @vat_sale_amount, @return_dtls_seq);"
                    + " select scope_identity()";
                strSeq = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }

            return strSeq;
        }

        public void UpdateVatDtls(M_FIM_Vat_Dtls info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@vat_dtls_seq", info.vat_dtls_seq);
                dbManager.AddParameters(1, "@vat_seq", info.vat_seq);
                dbManager.AddParameters(2, "@pay_seq", info.pay_seq);
                dbManager.AddParameters(3, "@vat_buy_amount", info.vat_buy_amount);
                dbManager.AddParameters(4, "@receive_seq", info.receive_seq);
                dbManager.AddParameters(5, "@vat_sale_amount", info.vat_sale_amount);
                dbManager.AddParameters(6, "@return_dtls_seq", info.return_dtls_seq);

                strSQL = @"update fim_vat_dtls
set pay_seq = @pay_seq
,vat_buy_amount = @vat_buy_amount
,receive_seq = @receive_seq
,vat_sale_amount = @vat_sale_amount
,return_dtls_seq = @return_dtls_seq
where vat_dtls_seq = @vat_dtls_seq";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteVatDtls(string vat_seq)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@vat_seq", vat_seq);

                strSQL = @"delete from fim_vat_dtls where vat_seq = @vat_seq";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public IList<M_FIM_Payments> GetReportPND(string strPaySeq)
        {
            IList<M_FIM_Payments> ret = new List<M_FIM_Payments>();
            DataTable drc = null;
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@strPaySeq", strPaySeq);

                strSQL = "select * from ("
                + " SELECT fr.pay_seq,fr.receipt_no,fr.pay_date"
                + " ,fr.contact_seq,case when imc.NameTh is null then imc.NameEng else imc.NameTh end as ContactName"
                + " ,Concat(case when imc.Address is null then '' else 'ที่อยู่ '+imc.Address end"
                + " ,case when imc.Lane is null then '' else ' '+imc.Lane end"
                + " ,case when imc.Road is null then '' else ' ถนน '+imc.Road end"
                + " ,case when imc.District is null then '' else ' '+imc.District end"
                + " ,case when imc.Amphur is null then '' else ' '+imc.Amphur end"
                + " ,case when imc.Province is null then '' else ' '+imc.Province end) as contact_addr"
                + " ,imc.ConGrpId as ContactGroup,iml.LookUpName ContactGroupName,imc.TaxCode"
                + " ,isnull(fr.ref_no,'') as ref_no,fr.pay_name,mrd.total_amount as amount"
                + " ,case when p.tax_rate is not null then p.tax_rate else case when imc.ConGrpId = 47 then 3 else 1 end end as tax_rate"
                + " ,isnull(fr.tax,0) as tax,fr.created_by,fr.created_when"
                + " ,mrd.total_amount as pr_amount, fr.vat"
                + " , case when fr.method_seq in (1, 3) then fr.real_pay_date"
                    + " else case when fr.method_seq = 2 then pc.pay_date"
                        + " else case when fr.method_seq = 4 then pip.pip_date"
                        + " else null"
                        + " end"
                    + " end"
                + " end as real_pay_date"
                + " FROM fim_payments p"
                + " left join fim_payments fr on p.pay_seq = fr.tax_pay_seq"
                + " left join (select pc.pay_seq, min(chq.pay_date) as pay_date"
                    + " from fim_payment_cheques pc"
                    + " inner join fim_cheques chq on pc.cheque_seq = chq.cheque_seq"
                    + " where pc.record_status <> 'D'"
                    + " and chq.record_status <> 'D'"
                    + " group by pc.pay_seq"
                    + ") pc on fr.pay_seq = pc.pay_seq"
                + " left join fim_payments_payinpack pip on fr.pay_seq = pip.pay_seq"
                + " LEFT JOIN IcmMasContact imc ON imc.ConId = fr.contact_seq"
                + " LEFT JOIN IcmMasLookUp iml ON iml.LookUpId = imc.ConGrpId"
                    //+ " LEFT JOIN fim_mas_events frt ON frt.event_seq = fr.event_seq"
                    // Adding for Total Amount on 4/6/57 ต้องการยอดรวม
                + " LEFT JOIN (select mrd.money_req_seq"
                        + ", max(coalesce(mrd.percent_vat, 0)) as percent_vat, max(coalesce(mrd.percent_tax, 0)) as percent_tax"
                        + ", sum(coalesce(mrd.amount, 0) - coalesce(mrd.amount_vat, 0)) as total_amount"
                        + ", sum(coalesce(mrd.amount_tax, 0)) as total_amount_tax"
                        + " from fim_money_request_dtls mrd"
                        + " where mrd.record_status not like 'D'"
                        //+ " and coalesce(mrd.bath_amount_tax, 0) > 0"
                        + " group by mrd.money_req_seq"
                        + ") mrd ON fr.money_req_seq = mrd.money_req_seq"
                + " WHERE fr.record_status = 'A' AND fr.contact_seq is not null AND coalesce(fr.amount, 0) > 0"
                    //+ " WHERE fr.record_status != 'D' AND fr.contact_seq is not null AND fr.amount is not null AND fr.tax is not null "
                //+ (month != null ? " AND MONTH(fr.pay_date) = @Month" : "")
                //+ (year != null ? " AND YEAR(fr.pay_date)  = @Year" : "")
                //+ (source != null ? " AND fr.bgm_source_seq  = @source" : "")
                //+ (contact_type != null ? " AND imc.ConGrpId  = @Type" : "")
                + " and p.pay_seq = @strPaySeq)t"
                + " order by real_pay_date";
                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                if (drc.Rows.Count > 0)
                {
                    for (int i = 0; i < drc.Rows.Count; i++)
                    {
                        M_FIM_Payments item = new M_FIM_Payments();
                        item.remarks = drc.Rows[i]["contact_addr"].ToString();
                        item.contact_name = drc.Rows[i]["ContactName"].ToString();
                        item.amount = cn.ConvertNullDouble(drc.Rows[i]["amount"]);
                        item.tax = cn.ConvertNullDouble(drc.Rows[i]["tax"]);
                        item.contact_person_name = drc.Rows[i]["TaxCode"].ToString();
                        item.pay_date = cn.ConvertNullDateTime(drc.Rows[i]["pay_date"]);
                        item.ref_no = drc.Rows[i]["ref_no"].ToString();
                        item.pay_name = drc.Rows[i]["pay_name"].ToString();
                        item.tax_rate = cn.ConvertNullDouble(drc.Rows[i]["tax_rate"]);
                        //(drc[i]["tax"] != null ? Convert.ToDecimal(drc[i]["tax"]) : 0);
                        //item.tax_rate = (drc[i]["tax_rate"] != null ? Convert.ToDecimal(drc[i]["tax_rate"]) : 0);
                        //item.redemption_amount = cn.ConvertNullDouble(drc.Rows[i]["pr_amount"]);// แทน pr_amount
                        item.vat = cn.ConvertNullDouble(drc.Rows[i]["vat"]);
                        item.contact_type = cn.ConvertNullInt(drc.Rows[i]["ContactGroup"]);
                        item.real_pay_date = cn.ConvertNullDateTime(drc.Rows[i]["real_pay_date"]);
                        ret.Add(item);
                    }

                }
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                if (dbManager.Connection != null) dbManager.Dispose();
            }

            return ret;
        }
        
        #endregion

        public IList<M_BGM_MAS_Source> GetBgmSource()
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_BGM_MAS_Source> infoList = new List<M_BGM_MAS_Source>();
            try
            {
                dbManager.Open();

                strSQL = "select bgm_source_seq,bgm_source_name"
                    + " from bgm_mas_source"
                    + " where record_status = 'U'";


                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;

                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_BGM_MAS_Source info = new M_BGM_MAS_Source();
                        info.bgm_source_seq = Convert.ToInt32(drc[i]["bgm_source_seq"]);
                        info.bgm_source_name = drc[i]["bgm_source_name"].ToString();
                        infoList.Add(info);
                    }

                }

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public string GetNextRefNo(Nullable<int> bgm_year, Nullable<int> bgm_source_seq)//, string use_advance)
        {
            string strSQL;
            DataTable dtb = null;
            string strRefNo = "";
            string strResult = bgm_year.ToString() + "/0001"; ;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_year", bgm_year);

                strSQL = "SELECT mr.bgm_source_seq, mr.bgm_year, max(mr.ref_no) as ref_no"
                    + " FROM fim_payments mr"
                    + " WHERE mr.record_status not like @delete_status"
                    + (bgm_source_seq != null ? " and mr.bgm_source_seq = @bgm_source_seq" : "")
                    + (bgm_year != null ? "" : " and mr.bgm_year = @bgm_year")
                    + " group by mr.bgm_source_seq, mr.bgm_year";


                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                if (dtb.Rows.Count > 0)
                {
                    strRefNo = dtb.Rows[0]["ref_no"].ToString();

                    if (strRefNo.Length > 0)
                    {
                        strRefNo = strRefNo.Split('/')[1];
                        int nSequnce = Convert.ToInt32(strRefNo);
                        strResult = bgm_year.ToString() + "/" + (nSequnce + 1).ToString("0000");
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return strResult;
        }

        #region[pay in pack]
        public IList<M_FIM_Payments> GetNotRealPayment(int? bgm_source_seq, int? work_area_seq, int? bgm_year, int? method_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_Payments> infoList = new List<M_FIM_Payments>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@work_area_seq", work_area_seq);
                dbManager.AddParameters(3, "@bgm_year", bgm_year);
                dbManager.AddParameters(4, "@method_seq", method_seq);

                strSQL = "SELECT p.pay_seq, p.ref_no, mr.ref_no as ref_no_money_request, p.bgm_source_seq, p.work_area_seq, p.bgm_year, p.method_seq \r\n"
                    + ", p.pay_date, p.real_pay_date, p.pay_name, p.money_req_seq, p.amount, p.contact_seq \r\n"
                    + ", p.record_status, p.created_by, p.created_when, p.updated_by, p.updated_when \r\n"
                    + ", rs.record_status_name, m.method_name, s.bgm_source_name, mr.money_req_name  \r\n"
                    + ", IIF(c.NameTh is not null, c.NameTh \r\n"
                        + ", concat(np.prefix_name, emp.first_name, ' ', emp.last_name)) as contact_name \r\n"
                    + ", cb.ConBankId as to_bank_acc_id \r\n"
                    + ", IIF(cb.AccountName is null or cb.AccountCode is null,  \r\n"
                        + " concat(IIF(bb.bank_acc_name is null, concat(np.prefix_name, emp.first_name, ' ', emp.last_name), bb.bank_acc_name), ' (',bb.bank_acc_no, ')'),  \r\n"
                        + "Concat(cb.AccountName ,' (' ,cb.AccountCode,')')) as to_bank_acc_name \r\n"
                    + " FROM fim_payments p \r\n"
                        + " LEFT JOIN fim_mas_methods m ON p.method_seq = m.method_seq \r\n"

                        // จอย กับ Contact Bank
                        + " LEFT JOIN IcmMasContact c ON p.contact_seq = c.ConId \r\n"
                        + " LEFT JOIN IcmMasContactBank cb ON c.ConId = cb.ConId And p.to_bank_acc_id = cb.ConBankId \r\n"

                        // จอยกับ Emp Bank
                        + " left join hrm_ps_emp_book_banks bb on bb.emp_seq = p.emp_seq  \r\n"
                        + " left join hrm_ps_employees emp on bb.emp_seq = emp.emp_seq  \r\n"
                        + " left join mas_name_prefixes np on np.name_prefix_seq = emp.name_prefix_seq \r\n"

                        + " LEFT JOIN bgm_mas_source s ON p.bgm_source_seq = s.bgm_source_seq \r\n"
                        + " LEFT JOIN fim_money_requests mr ON p.money_req_seq = mr.money_req_seq \r\n"
                        + " LEFT JOIN sys_record_status rs ON p.record_status = rs.record_status \r\n"
                        + " LEFT JOIN fim_payments_payinpack pp ON p.pay_seq = pp.pay_seq \r\n"
                    + " WHERE p.record_status not like @delete_status \r\n"
                    + " AND NOT EXISTS (SELECT * FROM fim_payments_payinpack pp WHERE  p.pay_seq = pp.pay_seq and pp.record_status not like @delete_status) \r\n"
                    + (bgm_source_seq != null ? " AND p.bgm_source_seq = @bgm_source_seq \r\n" : "")
                    //+ (work_area_seq != null ? " AND p.work_area_seq = @work_area_seq" : "")
                    + (bgm_year != null ? " AND p.bgm_year = @bgm_year \r\n" : "")
                    + (method_seq != null ? " AND p.method_seq = @method_seq \r\n" : "")
                    + " ORDER BY p.pay_seq, p.pay_date";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_Payments info = new M_FIM_Payments();
                        info.pay_seq = Convert.ToInt32(drc[i]["pay_seq"]);
                        info.bgm_source_seq = Convert.ToInt32(drc[i]["bgm_source_seq"]); //cn.ConvertNullInt(drc[i]["bgm_source_seq"]);
                        info.work_area_seq = Convert.ToInt32(drc[i]["work_area_seq"]);
                        info.bgm_year = Convert.ToInt32(drc[i]["bgm_year"]);
                        info.method_seq = cn.ConvertNullInt(drc[i]["method_seq"]);
                        info.pay_date = cn.ConvertNullDateTime(drc[i]["pay_date"]);
                        info.real_pay_date = cn.ConvertNullDateTime(drc[i]["real_pay_date"]);
                        info.pay_name = drc[i]["pay_name"] == null ? "" : drc[i]["pay_name"].ToString();
                        info.money_req_seq = cn.ConvertNullInt(drc[i]["money_req_seq"]);
                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        info.contact_seq = cn.ConvertNullInt(drc[i]["contact_seq"]);
                        //info.petition_seq = cn.ConvertNullInt(drc[i]["petition_seq"]);
                        //info.bidder_seq = cn.ConvertNullInt(drc[i]["petition_seq"]);
                        info.record_status = drc[i]["record_status"] == null ? "" : drc[i]["record_status"].ToString();
                        info.record_status_name = drc[i]["record_status_name"] == null ? "" : drc[i]["record_status_name"].ToString();
                        info.money_req_name = drc[i]["money_req_name"] == null ? "" : drc[i]["money_req_name"].ToString();
                        info.method_name = drc[i]["method_name"] == null ? "" : drc[i]["method_name"].ToString();
                        info.bgm_source_name = drc[i]["bgm_source_name"] == null ? "" : drc[i]["bgm_source_name"].ToString();
                        info.contact_name = drc[i]["contact_name"] == null ? "" : drc[i]["contact_name"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);
                        info.to_bank_acc_id = cn.ConvertNullInt(drc[i]["to_bank_acc_id"]);
                        info.to_bank_acc_name = drc[i]["to_bank_acc_name"] == null ? "" : drc[i]["to_bank_acc_name"].ToString();
                        info.ref_no = drc[i]["ref_no"] == null ? "" : drc[i]["ref_no"].ToString();
                        info.ref_no_money_request = drc[i]["ref_no_money_request"] == null ? "" : drc[i]["ref_no_money_request"].ToString();

                        infoList.Add(info);
                    }
                }


            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_FIM_Payments> GetRealPaid(string pip_ref_no)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_Payments> infoList = new List<M_FIM_Payments>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@pip_ref_no", pip_ref_no);
                //dbManager.AddParameters(2, "@work_area_seq", work_area_seq);
                //dbManager.AddParameters(3, "@bgm_year", bgm_year);
                //dbManager.AddParameters(4, "@method_seq", method_seq);

                strSQL = "SELECT p.pay_seq, p.ref_no as pay_ref_no, req.ref_no as req_ref_no, p.bgm_source_seq, p.work_area_seq, p.bgm_year, p.method_seq"
                    + ", p.pay_date, p.real_pay_date, p.pay_name, p.money_req_seq, p.amount, p.contact_seq, p.emp_seq"
                    + ", p.record_status, p.created_by, p.created_when, p.updated_by, p.updated_when"
                    + ", rs.record_status_name, m.method_name, s.bgm_source_name, mr.money_req_name"//, c.NameTh as contact_name"
                    //+ ", cb.ConBankId as to_bank_acc_id"
                    //+ ", IIF(cb.AccountName is null or cb.AccountCode is null, '', Concat(cb.AccountName ,' (' ,cb.AccountCode,')')) as to_bank_acc_name"
                    //+ ", case when p.contact_seq is not null then p.contact_seq"
                    //        + " else p.emp_seq"
                    //    + " end as contact_seq"
                    + ", case when p.contact_seq is not null then c.NameTh"
                            + " else concat(np.prefix_short, emp.first_name, ' ', emp.last_name)"
                        + " end as contact_name"
                    + ", case when p.contact_seq is not null then cb.ConBankId"
                            + " else empb.book_seq"
                        + " end as to_bank_acc_id"
                    + ", case when p.contact_seq is not null then IIF(cb.AccountName is null or cb.AccountCode is null, '', Concat(cb.AccountName ,' (' ,cb.AccountCode,')'))"
                            + " else Concat(np.prefix_short, emp.first_name, ' ', emp.last_name, ' (' ,empb.bank_acc_no,')')"
                        + " end as to_bank_acc_name"
                    + " FROM fim_payments p"
                        + " LEFT JOIN fim_mas_methods m ON p.method_seq = m.method_seq"
                        + " LEFT JOIN IcmMasContact c ON p.contact_seq = c.ConId"
                        + " LEFT JOIN fim_payments_payinpack pp ON p.pay_seq = pp.pay_seq"
                        + " LEFT JOIN IcmMasContactBank cb ON pp.to_bank_acc_id = cb.ConBankId /*c.ConId = cb.ConId And p.to_bank_acc_id = cb.ConBankId*/"
                        + " LEFT JOIN bgm_mas_source s ON p.bgm_source_seq = s.bgm_source_seq"
                        + " LEFT JOIN fim_money_requests mr ON p.money_req_seq = mr.money_req_seq"
                        + " LEFT JOIN sys_record_status rs ON p.record_status = rs.record_status"
                        + " LEFT JOIN fim_money_requests req on p.money_req_seq = req.money_req_seq"
                        + " LEFT JOIN hrm_ps_employees emp ON p.emp_seq = emp.emp_seq"
                        + " LEFT JOIN mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq"
                        + " LEFT JOIN hrm_ps_emp_book_banks empb ON p.to_bank_acc_id = empb.book_seq"
                    + " WHERE p.record_status not like @delete_status and pp.pip_ref_no = @pip_ref_no and pp.record_status not like @delete_status"
                    //+ " AND NOT EXISTS (SELECT * FROM fim_payments_payinpack pp WHERE  p.pay_seq = pp.pay_seq and pp.record_status not like @delete_status)"
                    //+ (bgm_source_seq != null ? " AND p.bgm_source_seq = @bgm_source_seq" : "")
                    //+ (work_area_seq != null ? " AND p.work_area_seq = @work_area_seq" : "")
                    //+ (bgm_year != null ? " AND p.bgm_year = @bgm_year" : "")
                    //+ (method_seq != null ? " AND p.method_seq = @method_seq" : "")
                    + " ORDER BY p.pay_seq, p.pay_date";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_Payments info = new M_FIM_Payments();
                        info.pay_seq = Convert.ToInt32(drc[i]["pay_seq"]);
                        info.bgm_source_seq = Convert.ToInt32(drc[i]["bgm_source_seq"]); //cn.ConvertNullInt(drc[i]["bgm_source_seq"]);
                        info.work_area_seq = Convert.ToInt32(drc[i]["work_area_seq"]);
                        info.bgm_year = Convert.ToInt32(drc[i]["bgm_year"]);
                        info.method_seq = cn.ConvertNullInt(drc[i]["method_seq"]);
                        info.pay_date = cn.ConvertNullDateTime(drc[i]["pay_date"]);
                        info.real_pay_date = cn.ConvertNullDateTime(drc[i]["real_pay_date"]);
                        info.pay_name = drc[i]["pay_name"] == null ? "" : drc[i]["pay_name"].ToString();
                        info.money_req_seq = cn.ConvertNullInt(drc[i]["money_req_seq"]);
                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        info.contact_seq = cn.ConvertNullInt(drc[i]["contact_seq"]);
                        info.emp_seq = cn.ConvertNullInt(drc[i]["emp_seq"]);
                        //info.petition_seq = cn.ConvertNullInt(drc[i]["petition_seq"]);
                        //info.bidder_seq = cn.ConvertNullInt(drc[i]["petition_seq"]);
                        info.record_status = drc[i]["record_status"] == null ? "" : drc[i]["record_status"].ToString();
                        info.record_status_name = drc[i]["record_status_name"] == null ? "" : drc[i]["record_status_name"].ToString();
                        info.money_req_name = drc[i]["money_req_name"] == null ? "" : drc[i]["money_req_name"].ToString();
                        info.method_name = drc[i]["method_name"] == null ? "" : drc[i]["method_name"].ToString();
                        info.bgm_source_name = drc[i]["bgm_source_name"] == null ? "" : drc[i]["bgm_source_name"].ToString();
                        info.contact_name = drc[i]["contact_name"] == null ? "" : drc[i]["contact_name"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);
                        info.to_bank_acc_id = cn.ConvertNullInt(drc[i]["to_bank_acc_id"]);
                        info.to_bank_acc_name = drc[i]["to_bank_acc_name"] == null ? "" : drc[i]["to_bank_acc_name"].ToString();
                        info.ref_no = drc[i]["pay_ref_no"] == null ? "" : drc[i]["pay_ref_no"].ToString();
                        info.ref_no_money_request = drc[i]["req_ref_no"] == null ? "" : drc[i]["req_ref_no"].ToString();
                        
                        infoList.Add(info);
                    }
                }


            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_FIM_Payments> GetAllRealPayment(int? bgm_source_seq, int? work_area_seq, int? bgm_year, int? method_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_Payments> infoList = new List<M_FIM_Payments>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@work_area_seq", work_area_seq);
                dbManager.AddParameters(3, "@bgm_year", bgm_year);
                dbManager.AddParameters(4, "@method_seq", method_seq);

                strSQL = "SELECT p.pay_seq, p.bgm_source_seq, p.work_area_seq, p.bgm_year, p.method_seq"
                    + ", p.pay_date, p.real_pay_date, p.pay_name, p.money_req_seq, p.amount, p.contact_seq"
                    + ", p.record_status, p.created_by, p.created_when, p.updated_by, p.updated_when"
                    + ", rs.record_status_name, m.method_name, s.bgm_source_name, mr.money_req_name, c.NameTh as contact_name"
                    + " FROM fim_payments p"
                        + " LEFT JOIN fim_mas_methods m ON p.method_seq = m.method_seq"
                        + " LEFT JOIN IcmMasContact c ON p.contact_seq = c.ConId"
                        + " LEFT JOIN bgm_mas_source s ON p.bgm_source_seq = s.bgm_source_seq"
                        + " LEFT JOIN fim_money_requests mr ON p.money_req_seq = mr.money_req_seq"
                        + " LEFT JOIN sys_record_status rs ON p.record_status = rs.record_status"
                        + " LEFT JOIN fim_payments_payinpack pp ON p.pay_seq = pp.pay_seq"
                    + " WHERE p.record_status not like @delete_status"
                    //+ " AND NOT EXISTS (SELECT * FROM fim_payments_payinpack pp WHERE  p.pay_seq = pp.pay_seq and pp.record_status not like @delete_status)"
                    + (bgm_source_seq != null ? " AND p.bgm_source_seq = @bgm_source_seq" : "")
                    + (work_area_seq != null ? " AND p.work_area_seq = @work_area_seq" : "")
                    + (bgm_year != null ? " AND p.bgm_year = @bgm_year" : "")
                    + (method_seq != null ? " AND p.method_seq = @method_seq" : "")
                    + " ORDER BY p.pay_seq, p.pay_date";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_Payments info = new M_FIM_Payments();
                        info.pay_seq = Convert.ToInt32(drc[i]["pay_seq"]);
                        info.bgm_source_seq = Convert.ToInt32(drc[i]["bgm_source_seq"]); //cn.ConvertNullInt(drc[i]["bgm_source_seq"]);
                        info.work_area_seq = Convert.ToInt32(drc[i]["work_area_seq"]);
                        info.bgm_year = Convert.ToInt32(drc[i]["bgm_year"]);
                        info.method_seq = cn.ConvertNullInt(drc[i]["method_seq"]);
                        info.pay_date = cn.ConvertNullDateTime(drc[i]["pay_date"]);
                        info.real_pay_date = cn.ConvertNullDateTime(drc[i]["real_pay_date"]);
                        info.pay_name = drc[i]["pay_name"] == null ? "" : drc[i]["pay_name"].ToString();
                        info.money_req_seq = cn.ConvertNullInt(drc[i]["money_req_seq"]);
                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        info.contact_seq = cn.ConvertNullInt(drc[i]["contact_seq"]);
                        //info.petition_seq = cn.ConvertNullInt(drc[i]["petition_seq"]);
                        //info.bidder_seq = cn.ConvertNullInt(drc[i]["petition_seq"]);
                        info.record_status = drc[i]["record_status"] == null ? "" : drc[i]["record_status"].ToString();
                        info.record_status_name = drc[i]["record_status_name"] == null ? "" : drc[i]["record_status_name"].ToString();
                        info.money_req_name = drc[i]["money_req_name"] == null ? "" : drc[i]["money_req_name"].ToString();
                        info.method_name = drc[i]["method_name"] == null ? "" : drc[i]["method_name"].ToString();
                        info.bgm_source_name = drc[i]["bgm_source_name"] == null ? "" : drc[i]["bgm_source_name"].ToString();
                        info.contact_name = drc[i]["contact_name"] == null ? "" : drc[i]["contact_name"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);
                        infoList.Add(info);
                    }
                }


            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }
        #endregion[pay in pack]

        #region[pnd print out]
        public M_FIM_PaymentRecordPrints GetNextDocNoPND(string doc_type)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_FIM_PaymentRecordPrints info = new M_FIM_PaymentRecordPrints();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                //dbManager.AddParameters(0, "@book_no", book_no);
                dbManager.AddParameters(0, "@doc_type", doc_type);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@print_flag", 1);

                strSQL = "SELECT ISNULL((MAX(doc_no)),0) as doc_no, ISNULL((MAX(book_no)),0) as book_no"
                        + " FROM FimPaymentRecordPrints"
                    + " WHERE record_status != @record_status and print_flag = @print_flag"
                    + " and doc_type = lower(@doc_type)";
                //  + " and book_no = @book_no";


                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.doc_no = Convert.ToInt32(drc[0]["doc_no"]);
                    info.book_no = Convert.ToInt32(drc[0]["book_no"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public M_FIM_PaymentRecordPrints GetPNDPrintInfo(int ref_id, string doc_type)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_FIM_PaymentRecordPrints info = new M_FIM_PaymentRecordPrints();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@doc_type", doc_type);
                dbManager.AddParameters(1, "@ref_id", ref_id);

                strSQL = "SELECT id, doc_type, book_no, doc_no, print_flag"
                        + " FROM FimPaymentRecordPrints"
                    + " WHERE record_status != 'D'"
                    + (doc_type != null ? " and doc_type = @doc_type" : "")
                    + (ref_id != null ? " and ref_id = @ref_id" : "");


                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.id = Convert.ToInt32(drc[0]["id"]);
                    info.doc_type = drc[0]["doc_type"].ToString();
                    info.book_no = Convert.ToInt32(drc[0]["book_no"]);
                    info.doc_no = Convert.ToInt32(drc[0]["doc_no"]);
                    info.print_flag = Convert.ToInt32(drc[0]["print_flag"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public string InsertPNDPrint(M_FIM_PaymentRecordPrints model)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(9);

                dbManager.AddParameters(0, "@ref_id", model.ref_id);
                dbManager.AddParameters(1, "@doc_type", model.doc_type.ToLower());
                dbManager.AddParameters(2, "@book_no", model.book_no);
                dbManager.AddParameters(3, "@doc_no", model.doc_no);
                dbManager.AddParameters(4, "@created_by", model.created_by);
                dbManager.AddParameters(5, "@created_when", model.created_when);
                dbManager.AddParameters(6, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(7, "@ref_no", model.ref_no);
                dbManager.AddParameters(8, "@print_flag", model.print_flag);

                strSQL = "INSERT INTO FimPaymentRecordPrints"
                    + " (ref_id,ref_no,doc_type,book_no,doc_no,print_flag,created_by,created_when,record_status)"
                    + " VALUES(@ref_id,@ref_no,@doc_type,@book_no,@doc_no,@print_flag,@created_by,@created_when,@record_status);"
                    + " select scope_identity()";

                var strSeq = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();
                return strSeq;

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

        }
        #endregion[pnd print out]

    }
}
